Forum Discussion

SamGriswold's avatar
SamGriswold
Copper Contributor
Oct 13, 2022
Solved

IF= Formula not allowing SUM

In cell G4 I need to input the employees penetration % for a particular bonus 0-74.99% = -$750, 75-99.9% = $0, and 100%+ = $750

Here is the Formula I used in cell L4 where I need the $$ amount:
=IF(G4<75%,"-$750",IF(G4<100%,"$0",IF(G4>100%,"$750")))

 

It calculates properly as far showing the proper $$, but the amount will not factor into the SUM (5 total cells for various commissions and bonuses) all others are working properly (cell is set to currency not text)

 

Here is the spreadsheet:

https://docs.google.com/spreadsheets/d/1G85iH5XvMRWGQKTgP34KI0CZu10nGEPB/edit?usp=sharing&ouid=100538389534669388186&rtpof=true&sd=true 

 

Any assistance would be appreciated!!

 

 

 

  • SamGriswold 

     

    Remove currency symbol and double-quotes around values.  The formula should be written:

     

    IF(G4<75%,-750,IF(G4<100%,0,IF(G4>=100%,750)))

    or better:

    IF(G4<75%,-750,IF(G4<100%,0,750))

     

    (The opposite of "<" is ">=", not ">".)

  • JoeUser2004's avatar
    JoeUser2004
    Bronze Contributor

    SamGriswold 

     

    Remove currency symbol and double-quotes around values.  The formula should be written:

     

    IF(G4<75%,-750,IF(G4<100%,0,IF(G4>=100%,750)))

    or better:

    IF(G4<75%,-750,IF(G4<100%,0,750))

     

    (The opposite of "<" is ">=", not ">".)

Resources