Mar 29 2022 10:01 AM
Regular: 34
Prevailing: 2.50
Shop: 6.00
----------
Total: 42.50
That's example of data. Now, since the total is over 40, subtract 2.50 from SHOP and give new balance.
Anyone have ideas on this? Thank you in advance,
Drezna.
Mar 29 2022 10:04 AM
SolutionMar 29 2022 10:59 AM
=IF(SUM(F20:F24)>40,IF(F25-40<F23,F23-SUM(F25-40),F20-(F25-40-F23)),"total <= 40")
Is this what you want to do? I entered above formula in cell C23 and copied it across range C23:E23.
Mar 29 2022 11:16 AM
@OliverScheurich Thank you for your reply. I'm sharing my file to show you how far I've gotten on resolving this.
https://1drv.ms/x/s!An-umg36SSwYk7M25UCn0v0RREuhrg?e=KkiXFJ
Thank you
Drezna.
Mar 30 2022 06:15 AM
You can try the attached file. This could be what you want to do.
Mar 30 2022 06:19 AM
@OliverScheurich Absolutely perfect! Thank you so much for your help!
Mar 30 2022 06:37 AM
Variants could be
=SUM(H5:H10) - (G29>40)*(G28>H16)*(G28-H16)
=(H16-G28)*(G29<=40)*(G28<=H16)
Mar 29 2022 10:04 AM
Solution