Calculating rebates that are prioritised by a ratio

Hi,

I have a quandary I need help with.

Let's say that when a customer buys 30K of software and 70K of hardware, they get a rebate of 1% on the hardware and 5% on the software. Simple enough right?

What if the total minimum order value has to be 80k across HW and SW and the ratio they need to maintain is 70%, 30%? In this example, the deal is over 80k, SW is 30% and HW is 70%. The rebate is 1% of 30K or $300 and 5% of 70K which is 3.5k

Now say that the deal total deal is 200K (170K HW and 30K SW). The deal over 80k which is fine, but the HW value needs to be adjusted back to 70K to maintain the 70%, 30% split (as either the hw or the sw portion needs to equal 30% of the total). In this case the rebate is 1% of 70K or $700 and 5% of 30K or 1.5k

What if the deal was 140K (70K HW and 70K SW). The deal again is over 80K but we need to adjust the HW (not the SW as it attracts a larger rebate), down to 30K, so that we maintain a 70%/30% split. The rebate is 1% of 30K or $300 and 5% of 70K which is 3.5k

I've tried several IF OR Arguments to create a spreadsheet that enables the user to enter in the HW and SW value and get a calculated rebate. I can't make it work and it's driving me nuts.

Any help or thoughts would be greatly appreciated!

HW in B3: 70000
SW in C3: 30000
TOTAL in D3: =B3+C3

REBATE in E3: =IF(D3<80000, 0, IF(MAX(B3:C3)/D3>=70%,(((MIN($B3:$C3)/30)*70)*5%)+(MIN($B3:$C3)*1%), (((MAX($B3:$C3)/70)*30)*1%)+(MAX($B3:$C3)*5%)))

Copy C3:D3 down, then fill in some more examples as per your scenarios above.

We can thus completely replace the IF test at the beginning to do that instead:

=IF((MIN(B3:C3)/30)<800, 0, IF(MAX(B3:C3)/D3>=70%,(((MIN($B3:$C3)/30)*70)*5%)+(MIN($B3:$C3)*1%), (((MAX($B3:$C3)/70)*30)*1%)+(MAX($B3:$C3)*5%)))


Or even.... =IF(MIN(B3:C3)<24000, 0, .....

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Getting uglier every time:

=IF(OR(MIN(B3:C3)<24000,SUM(B3:C3)<80000), 0, IF((C3/30)*70<=B3, (C3*5%) + ((C3/30)*70)*1%, IF((B3/30)*70<=C3, (B3*1%)+((B3/30)*70)*5%, (((MAX($B3:$C3)/70)*30)*1%)+(MAX($B3:$C3)*5%))))

If you examine the RED, BLUE and ORANGE sections of that formula above, you notice there are two separate formulas in each section separated by a +. Those are your two sections for hardware and software. So put that formula in two adjacent cells, then remove one half the colored section in cell to separate out the values.