# Mulitiplying In Formula Giving Incorrect Answer

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.

ADVERTISEMENT

## Date And Time Calculation Giving Incorrect Answer

Nov 21, 2011

The following formula seems to be incorrect. I typed in the times and dates to get the difference in hours, but my times are in 5 minute blocks, so the answer is given doesnt add up

Start DateFinsh date Hours15/11/2011 13:5017/11/2011 2:2536.58

## If Statement Providing Incorrect Answer?

May 20, 2014

In cell K8 is the value (General) 800. In C8 is the value (Also general format) 768.

=IF(C8

## Addition Calculation Not Giving Correct Answer

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).

## SUMIF - Trim And Concatenate 2 Sets Of 2 Columns Giving Answer In Another Column

Jun 22, 2008

I have two sets of two columns which contain numbers as text with trailing spaces and I want to concatenate column A and B and concatenate column F and G, and then compare them, and sumif value in column I if they are the same - giving the answer in the same row as the matching data in column A and B.. but in column J.

I�ve seen sum product and haven�t been able to get it to work at all. I have been adding the trim() function in around the column references to see if that would work but am failing�

A = 2000 text
B = 1234 text

F = 2000 trailing spaces, text
G = 1234 trailing spaces, text

I = 10,000

Answer in column J

I want 20001234 = 20001234 = 10,000 for the whole column.

I don't want to change the original data as its externally sourced and used for other links etc.. This would save a few hours a week if i ( more correctly, you ) could get this to work.

## 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.

## Formula Not Giving Correct Output?

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

## Formula Not Giving Correct Output?

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

## Sum Formula Returns Incorrect Result

Dec 1, 2009

I have created a table in excel, and it has a very simple sum totals. When i include all cells in the table the total becomes 0. When I exclude a cell in the table, the total shows. I have deleted the row and re inserted another one and copied the cell content from above that was not having a problem,

## Same Formula, Why Can't Get The Same Answer??

Jun 4, 2009

in the "Order for EMS" and "Order for FEDEX" there have 1 product exist, now the customer need to type the country in the "F35", and the Price of weight will look back to the sheet "EMS Weight" and "FEDEX Weight" but for example, if i type Japan in both sheet, the EMS that one is correct 1kg to Japan is \$168 but in FEDEX that one is not correct 1kg to Japan is \$169 but now show \$342, the formula in "G36" should be correct, what's wrong of my formula????

## Lookup Formula Finding Incorrect Results

Nov 3, 2009

If I type in an age in A1, in A2 I want an age group to appear

the groups are
20-24
25-29
30-34
35-39
40-44
45-49
50-54
55-59
60-65

so far, if I type in an age then it normally enters the group ABOVE what I want

i.e. if I enter �24� then it put the group �25-29�. if I enter �42� it puts �45-49� and so on.

=LOOKUP(\$A1,{0,16,20,25,30,35,40,45,50,55,60},{"<16","20-24","25-29","30-34","35-39","40-44","45-49","50-54","55-59","60+"})

## Simple If Greater Than Formula Giving Error

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] ........

## Formula Returns Incorrect Results When Copied

Aug 4, 2006

My goal is to sum up the total sales and total forecasted sales of each cost center. I compiled a formula that is supposed to do this just as I want it to. Simple enough. However, for some odd reason the formula returns either the totals of ALL cost centers or just a zero instead of the correct result. The weird part is that when I check the formula by pressing the "insert formula" button it suggests that the formula actually should produce the desired result.

## Conditional Format Formula Producing Incorrect Results?

Jan 20, 2014

I'm trying to do a simple conditional format where the date is highlighted in red if the difference between that date and Todays date is greater than 90 days.

I've done the formula as =(TODAY()>\$B5)>90, which appears to work in the sheet and shows either True or False but when inputting that formula into the conditional format it highlights every single cells.

Are conditional formatting formulas different?

## NetWorkDays Formula :: Giving 0 Instead Of 1 (leads To Negative Values)

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 doesn�t work. I tried many things. Love to get some other ideas why this is happening. It�s a altered Daddylongleg formula....

## Excel 2007 :: Named Formula Incorrect Results

Sep 4, 2012

Using Excel 2007.

I setup a named formula: Spread1 =Sheet1!\$E\$2:\$BE\$2 where I entered % values to spread a an annualized figure over 53 weeks

On Sheet 2 I entered the amount to spread in \$D\$2 and in \$E.. I entered my formula: =Spread1 * \$D\$2. Everything appeared great!

But then I deleted a few columns on sheet2 so first calculation is now in \$G\$2

Spread1: =Sheet1!\$E\$2:\$BE\$2
Calculations: Sheet2! \$G2:\$BG\$2 (the values in I,J,k while summing to the total are in the incorrect weeks based on the spread on Sheet1, BF and BG have #VALUE! errors)

Seems odd, if I highlight "Spread1" in the formula bar and [F9] I see an array of 53 possible values and the 3 values I expect are in the place in the array (elements 5,6,7).

How I can defeat it with the possibility that a user may delete a column?

## Excel 2007 :: Why Does Using Cell References In Formula Give Incorrect Value

Sep 30, 2011

Surely it's user error, but it honestly seems like Excel is not calculating 2 numbers correctly. At work, we have office 2010 where it is not calc'ing correctly; at home I have 2007 and it's fine, but anyway, here's my situation:

Cell A1: 0.003529
Cell B1: 0.48

If i put in Cell C1: "=B1/A1" i get a value of 136.000000 But that's not correct, it should be 136.01587... But if in Cell C1 i put "=0.48/.003529" then i get 136.01587...which is correct.

Why does using cell references in my formula give me the incorrect value?

## IF Formula Returning Wrong Answer

May 1, 2014

I am trying to determine if I can write a formula that will read text in one cell, such as insurance and if true enter the amount from another cell and if false then 0. I have tried a IF statement by add the text to name manager. However, the answer is 0 rather than the amount in the selected cell. I might need to use a different formula or function.

## Parenthesis - Formula Give An Answer Of 178

Oct 27, 2005

The following formula shoud give an answer of 178. I think I am placing the
parenthesis in the wrong position.

The answer I get is 308669

=(((F22*71449)*0.4)+16147/C22)

Cell-F22 number is 1826
71449 is a constant number
0.4 is a constant number
16147 is a constant number
Cell-C22 is 10.8

## Formula Correct But Not Showing Answer

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

## If FORMULA: If The Mark Is Less Than Or Equal To 20, The Answer Is AVERAGE

May 19, 2009

I would like to use IF function, to get the answer, if the mark is less than or equal to 20, the answer is AVERAGE, if the mark is between 21 and 25 the asnwer is GOOD, if the mark is between 26 and 30 the asnswe is VERY GOOD, if the mark is above 30 then the asnwer must be EXCELLENT.

## Hiding Answer To Formula If One Cell Is Empty

Nov 5, 2009

Is there a way to have the answer to a formula display nothing if one of the cells have nothing. Example cell A3= A1*A2 ... it will naturally show 0.00 but even if both A1 and A2 are completely blank. Is it possible for A3 to show nothing if A1 or A2 or both contain nothing. I still want it to show 0.00 but only if that�s what is in the other cells.

## Formula That Will Check Multiple Criteria Before Returning Answer?

May 14, 2014

Attached worksheet which is an sales incentive plan. I need cell B69 to represent the amount in b68 unless neither b30 or b99 are > 0. In other words if even 1 of the cells b30 or b99 come back positive, you get the amount in b68. If it comes back 0 in both cells b30 and b99, then they get 0.

## Formula To Show Combine Answer And Text In 1 Cell From 2 Different

Oct 27, 2009

i need t oknow if i can have text added into 1 cell with having a formula with a answer in it as well,,,i have attached a sheet with better examples of what i mean.

## Formula To Enable A Letter To Provide A Word As Answer

Apr 4, 2009

I have the following letters in cells
D2= A
D3=G
D4 =L

These represent Accomodation,Groceries, and Liquor.
Starting at E12 I want a formula , to drag down the column, which will show either Accomodation, Groceries or Liquor depending on the letter I put in cell D12,

Eventually I will possibly need help to summarise these costs on another sheet with other data such as fuel costs and fuel analysis but this will get me started.

## IF Statement (formula Is Giving The "" Response Even Though I51 = (40.00) And L51 = 40.00)

Jun 10, 2009

The following formula is giving me the "" response even though I51 = (40.00) and L51 = 40.00. What am I missing?

## IF Giving Me NAME#

Dec 13, 2006

I must be using hte wrong command or I am dumb.

Cell B8 I have a validation list of either Hub, Router, N/A

On cell C8 I want if (B8=Hub,3COM,0) but I get a NAME error. Is it because it won't work with letters but it can make the calculation?

## Vlookup Giving 3n/a

Oct 14, 2009

IM USING VLOOKUP TO FIND a VALUE IN AN ADJACENT CELL, BUT IF THE VALUE ISNT LISTED THEN I WANT IT TO DO NOTHING. EXAMPLE

=VLOOKUP(1100,Tuesday!A:F,6)

BUT IF THERE IS NO 1100 TO LOOKUP THEN GIVE ME A 0 VALUE OR DO NOTHING

## Wildcard Incorrect

Dec 7, 2007

I have tried everything I can think of to get this to work.

I am searching column B for anything containing "Tease", then if found, look to see if column J contains a "W".

Excel doesn't report an error, but this doesn't work:

=SUMPRODUCT((B3:B40="*Tease*")*(J3:J40="W"))

## Giving '400' Error

Jun 19, 2008

Sub testDateCompare()
Dim nmiDate As String
nmiDate = CDate("23/4/08")
Dim origDate As String
origDate = CDate("23/04/2008 15:05")
origDate = Left(origDate, InStr(origDate, " "))

Dim minDate As String
minDate = WorksheetFunction.Min(origDate, nmiDate)
MsgBox minDate
End Sub

Giving '400' error!?

Copyrights 2005-15 www.BigResource.com, All rights reserved