Forum Discussion

PJBROWN3457's avatar
PJBROWN3457
Copper Contributor
Jan 17, 2023

Need help with formula

Hi community,  I could realLy use a hand with a formula,

 

E3 IS THE PROFIT PERCENT FOR SPECIFIC JOB,

THE SLIDING SCALE IS:

0-20% HE GETS 1% OF TOTAL PROFIT DOLLARS

21-30% HE GETS 2% OF TOTAL PROFIT DOLLARS

31-35% HE GETS 3% OF TOTAL PROFIT DOLLARS

35-45% HE GETS 4% OF TOTAL PROFIT DOLLARS

>45% HE GETS 5% OF TOTAL PROFIT DOLLARS.

 

I HAVE BEEN TRYING AN IFS FORMULA AND IT IS NOT WORKING

 

M3=IFS(E3<=.2,G3,[E3<=.30,H3,[E3<=.35,I3,[E3<=.45,J3,[E3>.45,K3]]]])

 

  • PJBROWN3457 

     

    Create a small lookup table:

    The reward percentage is

    =XLOOKUP(E3,$L$2:$L$6,$M$2:$M$6,"",1)

    With the total profit in F3, the reward amount is

    =F3*G3

    See the attached demo.

    (P.S. Please turn off your Caps Lock key)

Resources