Forum Discussion
Conditional Formulas
Please advise the formula
Perhaps the attached workbook solves your question. It holds an "all in one" solution, but it's is far from elegant.
=IF(SUM($H$1:H2)>10000,MIN(SUM($H$1:H2)-10000,H2),0)*0.25+(H2-IF(SUM($H$1:H2)>10000,MIN(SUM($H$1:H2)-10000,H2),0))*0.45It works, but hard to read and understand without dissecting in its individual elements. That's why I also included a step-by-step approach with some helper columns.
8 Replies
- Hello,
You asked similar question 4 hours ago..
https://techcommunity.microsoft.com/t5/excel/conditional-formulas/m-p/1291714#M58074 I'm going to assume you mean If H2 would exceed by 10,000 the sum of H coloum then multiply H2 by *0.25 otherwise should multiply by 0.45,
and I'm also assuming that H2 is at the top of the H column and that the H column extends from H3 to H100
then the formula you put in J2 would be =IF((H2-SUM(H3:H100))>9999,H2*0.25,H2*0.45)
- H387 is the sum of H column.
H2 should multiply by *0.45 till 10,000 of H387 but when it’s exceed to 10,000 then H2 should’ve multiply by *0.25, answer needs on J2.Perhaps the attached workbook solves your question. It holds an "all in one" solution, but it's is far from elegant.
=IF(SUM($H$1:H2)>10000,MIN(SUM($H$1:H2)-10000,H2),0)*0.25+(H2-IF(SUM($H$1:H2)>10000,MIN(SUM($H$1:H2)-10000,H2),0))*0.45It works, but hard to read and understand without dissecting in its individual elements. That's why I also included a step-by-step approach with some helper columns.
- I don't quite follow what you want your formula to do. Could you explain it a little more clearly?
- H387 is the sum of H column.
H2 should multiply by *0.45 till 10,000 of H387 but when it’s exceed to 10,000 then H2 should’ve multiply by *0.25, answer needs on J2.
