I couldn't think of a better title, sorry.
I have to do an ARC/RRC calculation based on the number of servers in my environment on a monthly basis. Currently for one month there are five columns involved to get to the net price.
Two of the five columns are simple one cell divided by the other.
The other three cells are very complex formulas.
As the pricing model is confidential I have shortened the formulas by more than half. Below is meant as an illustration of complexity, the point is not how they evaluate.
Cell N10:
=IF(AND(H10>=10,M10>=50%,M10<90%),"RRC",IF(AND(H10>=10,M10>110%,M10<=150%),"ARC"))
Cell O10:
=IF(OR(N10="Baseline Scenario",N10="Less than 10"),"Range-A",IF(AND(M10>110%,M10<=125%),"Range-B ARC"))
Cell P10:
=IF(O10="Range-A",(L10*I10),IF(O10="Range-B ARC",(((H10*110%)*I10)+((L10-(H10*110%))*J10))))
I need to do the above calculations for the next X years so if I do things the only way I know how, I'd end up with 60 columns per year. I thought about lambda functions but I figured I'd just end up with a massive if statement anyway. Or is there another solution I haven't been exposed to? Happy to learn
thank you
I have to do an ARC/RRC calculation based on the number of servers in my environment on a monthly basis. Currently for one month there are five columns involved to get to the net price.
Two of the five columns are simple one cell divided by the other.
The other three cells are very complex formulas.
As the pricing model is confidential I have shortened the formulas by more than half. Below is meant as an illustration of complexity, the point is not how they evaluate.
Cell N10:
=IF(AND(H10>=10,M10>=50%,M10<90%),"RRC",IF(AND(H10>=10,M10>110%,M10<=150%),"ARC"))
Cell O10:
=IF(OR(N10="Baseline Scenario",N10="Less than 10"),"Range-A",IF(AND(M10>110%,M10<=125%),"Range-B ARC"))
Cell P10:
=IF(O10="Range-A",(L10*I10),IF(O10="Range-B ARC",(((H10*110%)*I10)+((L10-(H10*110%))*J10))))
I need to do the above calculations for the next X years so if I do things the only way I know how, I'd end up with 60 columns per year. I thought about lambda functions but I figured I'd just end up with a massive if statement anyway. Or is there another solution I haven't been exposed to? Happy to learn
thank you