Attention: Please take a moment to consider our terms and conditions before posting.

Excel Help

LargeAddick
LargeAddick Posts: 32,561
in Not Sports Related

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. 

Comments

  • Covered_End_Lad
    Covered_End_Lad Posts: 5,725
    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
    Big_Bad_World Posts: 5,859

    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
    cabbles Posts: 15,255
    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
    Leeds_Addick Posts: 4,700
    edited April 2020
    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
    LargeAddick Posts: 32,561
    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
    Imnot Athletic Posts: 1,017
    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
    Leeds_Addick Posts: 4,700
    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
    Stig Posts: 29,024
    Don't forget Bodmas  ;)
  • Solidgone
    Solidgone Posts: 10,208
    I used to love using Excel but haven’t touched a spreadsheet since I retired last year. 
  • valleynick66
    valleynick66 Posts: 4,890
    What if the answer is zero. What output is required then? 
  • Sponsored links:



  • LargeAddick
    LargeAddick Posts: 32,561
    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
    AddicksAddict Posts: 15,791
    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
    Huskaris Posts: 9,849
    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
    Sarrf_London Posts: 523
    edited April 2020
    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
    AFKABartram Posts: 57,825
    I’d love to be clever
  • MrOneLung
    MrOneLung Posts: 26,853
    What’s this thread got to do with Nightingale Hospital?
  • BR3red
    BR3red Posts: 1,715

  • LargeAddick
    LargeAddick Posts: 32,561

    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
    thai malaysia addick Posts: 18,334
    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
    Leeds_Addick Posts: 4,700
    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)
  • Sponsored links:



  • LargeAddick
    LargeAddick Posts: 32,561
    thanks, think I'm a bit brain dead this morning …..
  • ShootersHillGuru
    ShootersHillGuru Posts: 50,619
    edited May 2020
    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
    MrOneLung Posts: 26,853
    everyone knows Afka is just the face of charltonlife

    he is the Southall to the mysterious Nimer over in Bermuda.
  • ShootersHillGuru
    ShootersHillGuru Posts: 50,619
    H E AFKA

Categories

“Marketplace”