Multiple 'IF' Formula

[JoeOnExcel]

Hi all,

I've been fully scratching my head with this one.

I am doing a spreadsheet on the 'length of stay' at a homeless charity with a view of reducing length of stay so we can get people into homes and, as a result, enable us to help more people. Our partners are given a bonus for meeting a minimum target for length of stay (the lower the number the better). There's also a stretch target where, if they meet it, they get minimum bonus PLUS a stretch bonus. However, to incentivise going beyond their minimums, we're also offering a £10 bonus per "bed day" saved for every day saved between minimum target and stretch target.

A1 = Current Length of Stay
A2 = Bonus for meeting minimum target
A3 = Minimum target for length of stay
A4 = Bonus for meeting stretch target
A5 = Stretch target for length of stay
A6 = Bed days saved
A7 = Total current award

I need a formula for A7 that can work out what their award would be currently based on their current length of stay rate. So for example if the minimum target is 18 and the stretch is 14 and your current rate is 16 you would be awarded minimum target bonus plus £10 per total days saved (let's say you saved 24 days, you'd get an additional £240 on top of the minimum target bonus). But if the min target is 18 and your rate is 18, you'd just get minimum. If your rate is 13, you'd get minimum plus stretch. If you don't at least meet minimum you'd get nothing.

At the minute, I've got a long IF statement but it's not calculating the £10 bonus part and only looking at Minimum and Stretch. I hope that makes sense and that someone can help!

=IF(A1<A3,A2,IF(OR(A1<=A5),A2+A4,IF(A1<=A3,A1>=A5,A6*10)))

 

[Joe4]

Welcome to the Board!

Please show us some sample data, showing different scenarios, along with your expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.