IF Statements

[ajb_1976]

Hi,
I currently have an IF statement that returns a value based on whether or not a value entered in a cell is greater or less than what exists in another cell. The code i am using is

=+IF(D59<H55,"You are "& SUM(H55-D59)&" under the recommended quote price","You are "& SUM(D59-H55)&" over the recommended quote price")

This works fine but the problems is that the values in question are currency amounts yet the IF statement returns the difference as a general number to 4decimal places. - Is there anyway I can add something to the statment to format the calculated difference into currency, or at least to 2dp?

Thanks,
Alan

 

[Brianwarnock]

You can insert your currency symbol in the Text part, and use Round(Sum(....),2) for the decimal places, but as the resulting cell format is a string you lose trailing 0s , I think.

Brian

 

[shades]

Use the TEXT function, and then you can specify whatever format you want.
________
New jersey medical marijuana dispensaries

 

[Brianwarnock]

Hey that's good Rich, not used that.

Brian

 

[lemo]

what if you just format the cell with your formula to whatever format you need?
i feel like it should work just fine.. although sounds a bit trivial.. maybe i'm missing something..
let us know.
l

 

[lemo]

duh, of course i am missing something! a complex output..
nevermind, TEXT it is.. something like TEXT(SUM(H55-D59),"$.00")..
it's monday..
l

 

[Elie00]

is there a certain number of IF statement i can't go beyond in a single cell?
i was trying to create multiple IF statement in a single cell but was stoped at the 7th IF due to an error in the statement allthought it was the exact replica of the one before it!! execpt for the result it should give.

 

[qafself]

You are limited to 7 IFs unless you are using 2007.

There are workarounds using LOOkUPs or named formulae or try restructuring the formula.

Post an an example of what you want to achieve or of your workbook

 

[wiklendt]

is there a certain number of IF statement i can't go beyond in a single cell?
i was trying to create multiple IF statement in a single cell but was stoped at the 7th IF due to an error in the statement allthought it was the exact replica of the one before it!! execpt for the result it should give.

i've also gotten around this limit in certain instances. for example, i can bunch a few criteria in together in an OR statement:

something like (watch out! air code!)

=if(B6<(OR(1,2,3,4),"below", "above")