Returning value if number falls between options and using in sumif formula

masterexcelbundle

New Member
Joined
Feb 9, 2022
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I have a tiered reward system where if my employees falls between a 4 tiered system they are paid a bonus. I am trying to work out a formula to work this out automatically.

So on 1 page I have the tiered system as

Under 98.4% = $0
98.5% - 98.99% = $50
99% - 99.99% = $150
100% = $300

They are also paid on what full time equivalent hours they work, barely any are full time, and the above numbers are based on full time employees.

Currently I am having to find the actual amount and I am entering the formula as c3=300*(a3) = as I manually entered in $300 however can I put in a formula that if cell B3 falls between X, it will return X value to then times by cell A3

a1b1c1
FTESep ErrorAmount
0.3625100.00%108.75


Thankyou in advance
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
=IF( B2<0.985, 0 , IF( B2<0.99, 50*A2 , IF( B2<1, 150*A2 , 300*a2 )))

Book6
ABCDE
1FTESep ErrorAmount
20.3625100.00%108.75108.75
30
40
Sheet1
Cell Formulas
RangeFormula
E2:E4E2=IF( B2<0.985, 0, IF( B2<0.99, 50*A2, IF( B2<1, 150*A2, 300*A2 )))
 
Upvote 0
Solution
Another option that you could test
Excel Formula:
=LOOKUP(B2,{0,0.985,0.99,1},{0,50,150,300})*A2
 
Upvote 0
You're welcome. Glad we could help. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top