Forum Discussion

Asghar_Abbas's avatar
Asghar_Abbas
Copper Contributor
Apr 08, 2020
Solved

Conditional Formulas

If H2 would exceed to 10,000 the sum of H coloum then multiply by *0.25 otherwise should multiply by 0.45, I need answer in J2. Please advise the formula
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Apr 09, 2020

    Asghar_Abbas 

    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.45

     

    It 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.

Savia's avatar
Savia
Iron Contributor
Apr 08, 2020
I don't quite follow what you want your formula to do. Could you explain it a little more clearly?
  • Asghar_Abbas's avatar
    Asghar_Abbas
    Copper Contributor
    Apr 08, 2020
    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.

Resources