# Need help with formula

Occasional Visitor

# 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]]]])

# Re: Need help with formula

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.