Forum Discussion
PJBROWN3457
Jan 17, 2023Copper Contributor
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]]]])
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)