Thought this was a simple formula

[Ann R.]

This is the first time I've been asked to do a formula in over a year and
since we got Vista. I have the following formula:

=IF(AND(F9=1, P9=3.5),0.654,0)*M9

In my spreadsheet F9 is 1 and P9 is 3.5, so I want it to multiply .654 by
M9. However, it is giving 0, which would be the answer if F9 was not 1 or P9
was not 3.5. I cannot see anything wrong in the formula. What am I missing?
Thanks in advance.

 

[John]

Hi
Try it this way =IF(AND(F9=1,P9=3.5),0.654*M9,0)
Best regards
John

 

[David Biddulph]

Perhaps there are decimal places which you aren't displaying in F9 or P9.?
You could calculate =F9-1 and =P9-3.5, and you could also see whether =F9=1
gives TRUE or FALSE, similarly for =P9=3.5
Are you also sure that M9 is non-zero?

 

[Max]

Possibly rounding issues, eg P9's actual value might be 3.500001?
One thought:
=IF(AND(F9=1, ROUND(P9,1)=3.5),0.654,0)*M9
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[xlmate]

your formula is fine.
Is F9, P9 and M9 really contain numbers and not text look like numbers?

try retype the 1, 3.5 and the number in M9 and fomat cell as number
or try =ISTEXT(F9) and other cell reference in an empty cell to test,
TRUE will be retrun if it is text
FALSE will be return if is number

HTH
--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.

Thank You

cheers, francis

 

[Danny Boy]

What is cell M9? If this is empty then answer will be 0....

 

[Ann R.]

Thanks for all the suggestions. I found that the below works fine IF I put
an actual number in M9, however, I currently have a formula (K9*8) in that
cell. I can go ahead and put numbers, but the point was to save having to
multiply all those different number by 8.

Does this give you all any new ideas?

 

[Sean Timmons]

=IF(AND(F9=1, P9=3.5),0.654,0)*K9*8 would do the same it seems...

So it should depend on what K9 has.

 

[joeu2004]

I found that the below works fine IF I put an actual
number in M9, however, I currently have a formula
(K9*8) in that cell.
[....]
Does this give you all any new ideas?

There is nothing wrong with your original formulation. The problem is
more like due to the actual contents of the cells involved (F9, P9 and/
or M9).

Max's comment (rounding issue) seems closest to the explantion. More
generally, what you see displayed in the cell might be a rounded
representation of the actual value. However, P9 might not be the only
culprit here; and rounding to one decimal place might not be the right
solution for your application.

But you have not provided sufficient information. For example, what
is in M9? What makes you think that M9*0.654 should not be zero,
especially when rounded according to the General format?

My suggestion: format F9, P9 and M9 (maybe even K9) as Scientific
with 14 decimal places. Post those values here. You might not
understand the format, but some of us will.

Also, it might be helpful if you posted the exact formula in M9. Cut-
and-paste from the "fx" field below the toolbars.


----- original posting -----

 

[xlmate]

your first formula is working correctly, it is not necessary to dissect.
since your cells are linked to others, the source cell need to have a number
in it, an example will be K9 need to have a number. IF K9 is also a
formula, you will have to find which cell make up the formula and that there
is a number in it...all the way backward to the first cell that you input a
number.

Hope I have explained the situation.
--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.

Thank You

cheers, francis

 

[Ron Rosenfeld]

This is the first time I've been asked to do a formula in over a year and
since we got Vista. I have the following formula:

=IF(AND(F9=1, P9=3.5),0.654,0)*M9

In my spreadsheet F9 is 1 and P9 is 3.5, so I want it to multiply .654 by
M9. However, it is giving 0, which would be the answer if F9 was not 1 or P9
was not 3.5. I cannot see anything wrong in the formula. What am I missing?
Thanks in advance.

Are the values in F9 and P9 derived from formulas, or are they directly
entered? If the former then try this:

=IF(AND(ROUND(F9,0)=1, ROUND(P9,2)=3.5),0.654,0)*M9
--ron