Correct Formula Not Giving Results?
Feb 25, 2014
Cell A3 on my SHEET1 has the following formula =DATE(YEAR(C1),MONTH(C1) + 1,DAY(C1). It goes on like this for the next 12 months (+2, +3, +4....).
Column B on my SHEET2 has One of the five product codes (A, B, C, D, E) and Column D on my SHEET2 has manually entered dates
I created the following condition for each product code under each month in SHEET1
=IF(MONTH(Sheet2!$D$1:$D$39)=MONTH(Sheet1!$A$3),COUNTIF(SHEET2!$B$2:$B$39,"=A"),"")
The formula only works for two product codes (A and B), and then yields blank cells.
View 5 Replies
ADVERTISEMENT
Mar 24, 2014
I have a fomula field in row 67 and 68 .Row 67 calculates the average and row 68 calculates the improvement i.e. average(row 68)- last row inserted(row 67). The values for improvements are not showing correct value. C68 seems to give correct value but B68 and D68 is giving wrong value.
B68=4.8-4.5 =0.3 but it shows 0.4
C68=14.9-15.1=-0.2
D68=11.3-10.2=1.1 but it shows 1.0
I have attached the sheet also.
Sheet 1.xlsx
View 11 Replies
View Related
Mar 24, 2014
I have a fomula field in row 67 and 68 .Row 67 calculates the average and row 68 calculates the improvement i.e. average(row 68)- last row inserted(row 67). The values for improvements are not showing correct value. C68 seems to give correct value but B68 and D68 is giving wrong value.
B68=4.8-4.5 =0.3 but it shows 0.4
C68=14.9-15.1=-0.2
D68=11.3-10.2=1.1 but it shows 1.0
I have attached the sheet also.
Sheet 1.xlsx - Speedy Share - upload your files here
View 2 Replies
View Related
Feb 20, 2009
I have a workbook with calculations for a sale less the assorted fees and at the end giving the final amount from a sale.
I have noticed that some of the rows are not giving the correct amount in them.
In other words the addition of some columns in that row are not adding up correctly. It is only off by 1 cent (either over or under), but I can't figure out why.
I have the feeling that I am going to want to kick myself when someone explains this to me (I just know that I know the answer but for the life of me I can't right now).
View 2 Replies
View Related
May 24, 2012
I'm trying to add the total product sold by a group of sales person for example : Chad + Javier. My actual worksheet includes 600 rows of salespeople and 20 rows of product, i'm using this simple example to get the message across.
I was using =sum(sumif(A1:A9,"Chad',D1:D9),sumif(A1:A9,"Javier",D1:D9)), the formula should have given 91, but instead is calculating 113...
Salers PersonProduct AProduct BProduct C
Chad567
Chad567
Chad567
Chad567
[Code] .....
View 1 Replies
View Related
Jan 24, 2007
I have a problem with isnumber giving me two different results. One time it will evaluate the following as true:
ISNUMBER(0)
and in the same sheet it will evaluate the same as false.
I need ISNUMBER to NOT recognise "0" as a number all the time.
View 9 Replies
View Related
Jan 8, 2009
I am making a company wide estimate sheet and having trouble with the drop down box. I have a drop down to pick the branch, but when I name the source it only says branch, even though I named the data on the branch sheet. I would like to be able to pick the branch initials and have it populate the phone and fax number under the main heading but am getting a error there.
Also for the labor is it possible to have it insert the correct labor into the formulas at the labor lines according to which branch is picked from the drop down.
View 4 Replies
View Related
Jun 5, 2012
So I am using a macro to do a particular task. Part of the macro's process is to delete some unwanted rows.
The code below was for my original macro which works absolutely fine:
Note: The ranges are fixed (i.e. column "AT" is reffered)
Code:
Sheets(1).Select
Dim Current As Worksheet
Dim Flt As String
Flt = Me.TextBox2.Value
For Each Current In Worksheets
With Current
With .Columns("AT")
.AutoFilter field:=1, Criteria1:="
View 3 Replies
View Related
Jan 20, 2009
I have a VBA macro that I pieced together* a couple of years ago to help with a pretty simple task. We have two excel sheets that pull in information daily from different sources. The macro copies and paste values for the worksheet, then 1) saves a copy of the values only worksheet with the current date, 2) resaves the values only worksheet as a .csv file that is then manually uploaded into our database.
View 3 Replies
View Related
Feb 15, 2010
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
View 4 Replies
View Related
Jan 5, 2014
Although I follow the usual procedure for Chart insertion, the resulted chart is missing data.
View 5 Replies
View Related
Mar 20, 2013
In an excel sheet, I am using a macro to filter. Macro code is as follows. When I run this macro, it returns 4 rows matching the criteria from 560 rows in the excel sheet.
VB:
ActiveSheet.Range("$A$1:$I$566").AutoFilter Field:=1, Criteria1:= _
">=11/11/2012 22:13", Operator:=xlAnd, Criteria2:="<=11/12/2012 6:47"
I want to use the same criteria in the vb.net code, to do same thru vb.net. But, when I run the same code in vb.net, it just returns only 1 row (1st row only).
VB:
[FONT=Consolas][SIZE=2][COLOR=#0000ff]xlsRange1.AutoFilter(Field:=1, Criteria1:=">=11/11/2012 22:13", Operator:=Excel.XlAutoFilterOperator.xlAnd, Criteria1:="<=11/12/2012 6:47")
This returns 1 instead of 4 expected.
View 2 Replies
View Related
Dec 20, 2009
I have a formula that is correct all the way up to adding the last *K9. here is an
example:
C9 a number 4 then add 1 to it which makes it 2 now * it by D9 which is the number 384 and the answer is 768 now * that by K9 which is 6 and you get 4608 I'm getting 2310 using this formula... =IF( C9=0, 0, C9+1*D9)*K9.
View 3 Replies
View Related
Jan 19, 2012
I'm getting a mismatch error on my If activecell.value > 5 Then portion of this code. I'm trying to say if the value in column AG is greater than 5 then create an email address from the first and last name. I've even made sure to copy and paste the formula results as values.
Code:
Sub CheckPFPDates()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim emails As String
Dim salution As String
Dim currenttime As Integer
[Code] ........
View 2 Replies
View Related
Oct 29, 2008
I am having an issue with this formula. The formula works for 85% of my data. I worked through the formula and found that the error occurs within the networksday part of the formula. For some reason the it gives me a 0 when it should be giving me a 1. This causes the solution to be a negative and create the error. I placed an example of when both the formula works and doesnt work. I tried many things. Love to get some other ideas why this is happening. Its a altered Daddylongleg formula....
View 6 Replies
View Related
May 9, 2006
I have a range that has formulas that are based on other fcells outside the range.
What I want to do is, if the cell has a value to remove the formula and paste the results. If the filed is blank (no results from the formula) to leave the formula in place.
There could be a marco to run when this process is needed.
example attached
View 9 Replies
View Related
Jul 27, 2006
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
View 2 Replies
View Related
Feb 27, 2012
I have two questions:
1. Is there a way cells with formulas cannot be tampered even if they are not locked? So when I enter a data on a cell with formula, the formula won't be erased? The sample below shows that C1 and C2 has formulas but let's say I enter a value on C1, the formula should still stay? Is this possible?
A B C
1 5 10 =SUM(A1:B10)
2 2 4 =SUM(A2:B2)
2. Let's say I have a total of 125 (A1) and I need to get the answer (B1) where it has 5 with No. 3 on top (subscript) of 5? How to create a formula for this (I hope I make sense)?
View 1 Replies
View Related
Jan 26, 2014
We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.
I envisage something like an output report:
#####################
149 entries
Column A - Date - OK
Column B - Customer_Phone - Errors (Should be 11 digits)
Row 21 - Customer_Phone - Error (Not 11 digits)
Row 108 - Customer_Phone - Error (Contains letters)
Column C - Outcome - OK
Please correct and re-check.
#####################
View 4 Replies
View Related
Jul 3, 2008
I have a table with 3 columns of dates and then a column with Set # that I
feel in the box #.
I need to see how many items processed for each set per day.
Example:
[url]
The problem is that it counts the correct amount but not with the correct
dates.
The formula that I use is:
=SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))
View 14 Replies
View Related
Nov 4, 2005
How can I correct this Time formula which says in the Tag that it produces a
negative Result and hence displays in cell as ####
=(INDEX($A18:$A33,MATCH(MIN(C18:C33),C18:C33,0)))-(60/1440)
What I am doing is selecting the field in A which equates to the lowest
value in C (C= hourly sales), then I am subtracting 60 mins from the value
selected in A. This in effect will give me my closing Times i.e. find the
cell with Zero Sales then subtract 1 hour to find what must be the closing
time (assuming of course that there is at least ?1 of sales per hour while
open).
My cells in A are formatted as h:mm AM/PM, as is the format in the formula
cell
View 23 Replies
View Related
Aug 5, 2014
correct formula related to dates;
Assume cell A1 contains a date in DD-MMM-YYYY format. I would like to have a formula that looks in A1 and compares to the current date (TODAYS Date) and if the value in A1 is todays date or greater return "YES" and if not return "No".
View 2 Replies
View Related
Nov 3, 2008
I have a column with numbers ranging from -5 to 250. I want to write a formula to correct the numbers in the next column. If the number is less than 0, I want to replace it with 0,if the number is greater than 150 i want to replace it with 150 and if the number is in the 0-150 range it should be kept the same. Is there a way to use inequalities in a formula to do this??
View 2 Replies
View Related
Mar 11, 2013
I am using this formula for long now
=SUMPRODUCT(('[Diesel Statement.xlsx]Nov 12'!$C$2:$C$251=$H$1)*'[Diesel Statement.xlsx]Nov 12'!$G$2:$G$251)
every month i just add a new sheet and change the month name in the formula to that eg
I changed it to
=SUMPRODUCT(('[Diesel Statement.xlsx]Dec 12'!$C$2:$C$251=$H$1)*'[Diesel Statement.xlsx]Dec 12'!$G$2:$G$251)
but now it is not calculating the answer is coming as #value!.
Then I tried to add up by
=VLOOKUP($H$1,'[Diesel Statement.xlsx]Dec 12'!$C$2:$G$215,5,TRUE)
but it does not add all the entries only one entry is shown.
I want the total of the column G. looking up for values in C = H1 value
View 7 Replies
View Related
Mar 18, 2008
Excel 2003 SP2
--------------
I have been programmatically setting the formulae in a worksheet. I have various formulae:
Example 1 - =COUNTIF(Data!M$6:M$9871,3)/$E$4
The cell is formatted Percentage, 2 decimal places.
Using the formula evaluator the value returned is 56.80%
The cell displays 56.80%, all is good!
Example 2 - =SUM(IF(Data!$J$6:$J$9871=$D8,IF(Data!$M$6:$M$9871=3,1,0)))/$E$8
The cell is formatted Percentage, 2 decimal places.
Using the formula evaluator the value returned is 53.37%
The cell displays 0.00% !?!
View 9 Replies
View Related
Mar 4, 2014
How to get correct value using vlookup formula in duplicate look up values.
Here i mentioned a eg;
VlookUp_DuplicateValue.JPG
View 8 Replies
View Related
Dec 17, 2009
I have a what I believe to be a consistant formula on lines S12-S18 for gathering the sumproducts for dates on column O. Only problem is that the sumproduct formula is not working on line S18 correctly. I keep looking it over and I can't figure out why it is not returning the correct amount for that cell.
Here is the formula I am using:
=SUMPRODUCT((TEXT($O$12:$O$500,"dddd")=R18)*1)
Please see attached line S18 for the error I am getting.
View 9 Replies
View Related
Mar 27, 2009
Attached you will find the spreadshhet in question. If you click on the click here button input the following info:
8.0
3508949
80
0
0
25
the sheet will auto populate all numbers. this part is working properly. MY issue is if you look at the Bad Debt number, the correct response should in cell f21 should be $636.46 because in order to hit the next level of pay, you must at least hit $3,424,082.00. I can not figure out how to get the correct formula that will put the right pay out. Basically in order to hit a certain level of payout you must hit that next number, even if you are a $1 higher you can not get the pay out. So for example if your bad debt is $3,424,083 you would be paid out at 100%, not 120%.
View 3 Replies
View Related
Dec 8, 2011
I want to create a Macro that uses IF statements to enter the CORRECT VALUE into COLUMN βQβ in the ACTIVE WORKSHEET. I am providing an example of what the data set looks like at the very bottom of this post. I want to use a Macro as oppose to Formula in the worksheet because I want to turn the Macro into an Excel Add-In.
I want the Macro to do the following THREE THINGS:
1. IF the Value in COLUMN L is β0β THEN enter βn/aβ into COLUMN βQβ
2. IF the Value in COLUMN L is β2β THEN enter βn/aβ into COLUMN βQβ
3. IF the Value in COLUMN L is β1β THEN use a formula that looks like this:
= O8 + ( ( O8 / P8 ) * (First SUM the HOURS in COLUMN O for all the ROWS that have the SAME VALUES in COLUMN C and H and a β2β in COLUMN L and then MULTIPLY that Result against those rows with a β1β in COLUMN L whose COLUMNS C and H values match up exactly with those of the Summed Hours). The RESULT of the FORMULA would be entered into COLUMN βQβ. Once you look at the example at the bottom of this post it will definitely start making sense. I highlighted rows 8 β 11 for you in red in the data set at the bottom of this post just to center the attention on the rows I am talking about.
Here is an example of how the formula will work:
I will use ROW 8 from the data set below as an example. From the dataset I know CELL O8 equals 10 and CELL P8 equals 76. Now I will SUM the HOURS in COLUMN O ROWS 10 and 11 for ALL ROWS that have the SAME VALUES in COLUMN C and H in this case the values are βTimesβ and βPMβ and contain a β2β in COLUMN L which happens to equal 3, then that 3 should only apply to those rows with a β1β in COLUMN L whose COLUMNS C and H values match up exactly with those of the Summed Hours. So now I plug that into the formula: =10+((10/76)*(3)) and MY RESULT which will go into COLUMN βQβ IS 10.395.
Here the DATA SET:
Col A Col B Col C Col D Col E Col F Col G Col H Col I Col J Col K Col L Col M Col N Col O Col P Col Q Col R Col S Col T
Row 1 B ID Name Org Div T Number Model Make S Function E Function Type Description P ID OG Hours Sum EA P Hours Sum P S Hours EQ
This is what is happening in Column Q Explanation of Formula
Row 2
Times
Cont
2
28 28 n/a
n/a If formula populates Column Q cells with "n/a" if Column L cells contain a "0" or "2".
Row 3
Times
Cont
1
404 1194 413.4740369
O3+((O3/P3)*28) You get the Sum "28" by adding all the "P Hours" in Column O that match these 3 values:
1. The "Org" value of "Times" in Column C
2. The "S Function" value "Cont" in Column H
3. The "P ID" value "2" in Column L
[Code] ...........
View 9 Replies
View Related
May 18, 2012
Users copy and paste source data from a report into worksheet 1 each month. Data from last month is deleted and data for the current month copied into worksheet 1.
I am trying to write a formula within worksheet 1 to check that data for the current reporting period only is in worksheet 1. For example all data from last month's reporting period has been removed and the only data in worksheet 1 is the current reporting period.
Reporting period is shown in two columns Year and Period number (1 to 12).
View 4 Replies
View Related
