Formula for calculating the value but only if within a range

[aimling]

Hello, self taught Excel student here, and now I am stuck.

I am trying to make a spreadsheet that automatically calculates my employee's variable commission. I need the cells to calculate the value of the revenue they have made between £17000 and £30000 (col2); between £30000 and £45000 (col3); and £45000 and above (col4).

For example
Person A made £20000 (A1). Her cells would read £3000 (A2); £0 (A3); £0 (A4)
Person B made £35000 (B1). Her cells would read £13000 (B2); £5000 (B3); £0 (B4)
Person C made £47000 (C1). Her cells would read £13000 (C2); £15000 (C3); £2000 (C4).

Can anyone recommend the formula to use?

I've tried SUM IFs which work when the cells falls within the range but then when the consultant's revenue gets higher it, it works within the new range, but the lower range cells starts to miscalculate.

 

[jasonb75]

Welcome to the board!

For A2, try =MAX(0,MIN(A1,30000)-17000)
For A3, =MAX(0,MIN(A1,45000)-30000)
For A4, =MAX(0,A1-45000)

 

[aimling]

Works beautifully!!! Thank you so much Jason, I was close to getting there by myself as I was debating around needing to cap the figure, I just forgot about the MAX function!

So simple yet so effective! Love it!

Thanks again!