Excel Help

LargeAddick

can anyone tell me what is wrong with this formula :

=IF((+I38-732)*(13.8/100)<0,"0.00"),IF((+I38-732)*(13.8/100)>0,)

what I'm trying to achieve is that if the answer is <0 then put 0.00 but if it's > than 0 then put the actual figure it is. any help much appreciated. 

Covered_End_Lad

I think you need a space after each comma and also ensure the cell is in "General" format rather than text etc.

Big_Bad_World

Remove the bracket after "0.00" and enter a true and false value at the end of the second 'IF' function. You've closed off the first IF function without a false value or the nesting of another IF.

I think. Haven't done Excel in a while.

cabbles

I photo’d these cheat sheets someone sent me once even though I don’t know how to use excel and have never had to use it.  

One day I might have to learn.  I would be lear

Leeds_Addick

LargeAddick said:

can anyone tell me what is wrong with this formula :

=IF((+I38-732)*(13.8/100)<0,"0.00"),IF((+I38-732)*(13.8/100)>0,)

what I'm trying to achieve is that if the answer is <0 then put 0.00 but if it's > than 0 then put the actual figure it is. any help much appreciated. 

Should be this: =IF((+I38-732)*(13.8/100)<0,"0.00",(+I38-732)*(13.8/100))

Edit: Explanation is that the formula is =IF(Test, what happens if test is true, what happens if test is false).

So your test is (+I38-732)*(13.8/100)<0

If the test is true you want "0.00" but you should probably just change this to 0.

If the test is false you want the result (+I38-732)*(13.8/100)

So just plug them in.

= IF((+I38-732)*(13.8/100)<0,0,(+I38-732)*(13.8/100))

LargeAddick

Leeds_Addick said:

LargeAddick said:

can anyone tell me what is wrong with this formula :

=IF((+I38-732)*(13.8/100)<0,"0.00"),IF((+I38-732)*(13.8/100)>0,)

what I'm trying to achieve is that if the answer is <0 then put 0.00 but if it's > than 0 then put the actual figure it is. any help much appreciated. 

Should be this: =IF((+I38-732)*(13.8/100)<0,"0.00",(+I38-732)*(13.8/100))

Edit: Explanation is that the formula is =IF(Test, what happens if test is true, what happens if test is false).

So your test is (+I38-732)*(13.8/100)<0

If the test is true you want "0.00" but you should probably just change this to 0.

If the test is false you want the result (+I38-732)*(13.8/100)

So just plug them in.

= IF((+I38-732)*(13.8/100)<0,0,(+I38-732)*(13.8/100))

and we have a winner !! Thanks Leeds_Addick, that is much appreciated.

Imnot Athletic

You can simplify it even more as (+I38-732)*(13.8/100) is only less than zero if (I38-732)<0 and 13.8/100 is 0.138, 


=if(I38-732<0,0,(I38-732)*0.138)  would work just as well

Thanks for bringing it up, reminded me to change the calcs to 732 from 719 in the forecast I'm reforecasting!

Leeds_Addick

Imnot Athletic said:

You can simplify it even more as (+I38-732)*(13.8/100) is only less than zero if (I38-732)<0 and 13.8/100 is 0.138, 


=if(I38-732<0,0,(I38-732)*0.138)  would work just as well

Thanks for bringing it up, reminded me to change the calcs to 732 from 719 in the forecast I'm reforecasting!

I think I38 is cell I-38 rather than 138

Stig

Don't forget Bodmas 

Solidgone

I used to love using Excel but haven’t touched a spreadsheet since I retired last year. 

valleynick66

What if the answer is zero. What output is required then? 

LargeAddick

valleynick66 said:

What if the answer is zero. What output is required then? 

Good point, I will amend formula when in work on Tuesday.

AddicksAddict

LargeAddick said:

valleynick66 said:

What if the answer is zero. What output is required then? 

Good point, I will amend formula when in work on Tuesday.

You want <=0 as the test, not <0, assuming you want to display 0 when the value is 0.

Huskaris

What's the relevance of the numbers? I'd personally probably have the formula calculating off a cell, looks like a lot of hard coded numbers there, or am I being stupid?

Sarrf_London

Basically when you use IF, you need two alternative answers separated by a comma.

Agree with Huskaris for a change. If you are hard-coding numbers, use a calculator.

AFKABartram

I’d love to be clever

MrOneLung

What’s this thread got to do with Nightingale Hospital?

BR3red

LargeAddick

where cell H24 is 3,500.00 I want cell I24 to display 2,500.00 as the answer, which it is so doing but on auto summing Column I it is not including the sum of I24 in the column total, why?

=IF((+H24)*(80/100)>=2500,"2,500.00",(+H24)*80/100)

any help appreciated ..

thai malaysia addick

LargeAddick said:

where cell H24 is 3,500.00 I want cell I24 to display 2,500.00 as the answer, which it is so doing but on auto summing Column I it is not including the sum of I24 in the column total, why?

=IF((+H24)*(80/100)>=2500,"2,500.00",(+H24)*80/100)

any help appreciated ..

It is treating "2500.00" as text and, therefore, counting it as zero. I tend to use VBA to program Excel so I am less familiar with worksheet functions but value("2500.00") seems to work. 

Leeds_Addick

LargeAddick said:

where cell H24 is 3,500.00 I want cell I24 to display 2,500.00 as the answer, which it is so doing but on auto summing Column I it is not including the sum of I24 in the column total, why?

=IF((+H24)*(80/100)>=2500,"2,500.00",(+H24)*80/100)

any help appreciated ..

I think by putting 2500 in quotations like you have done it reads it as a string and you can't sum strings. 

Just take out the quotations and it should work

=IF((+H24)*(80/100)>=2500,2500,(+H24)*80/100)

LargeAddick

thanks, think I'm a bit brain dead this morning …..

ShootersHillGuru

AFKABartram said:

I’d love to be clever

@AFKABartram You run a successful website. That’s quite clever. Just think if you had applied that energy into something else ? You could be a millionaire. Another Jeff Bezos. You could be our new dodgy owner. 

MrOneLung

everyone knows Afka is just the face of charltonlife

he is the Southall to the mysterious Nimer over in Bermuda.

ShootersHillGuru

H E AFKA