|
Building complex formulas in EXCEL |
|
Excel Formals can calculate many logical condition and give
desired expected results. When a requirement comes across to
resolve a complex issue one can require big complex formula to
calculate the result after checking the inputs in set of
conditions etc. |
|
Then what to do, leave the work and calculate over it manually |
|
Our solution to such situation: Modularisation |
|
When a complex task is to be solved then try to break in parts.
Modularize it then using this small parts (modules) calculate
the final Result. How? |
|
Try to realise and break each problem in small set of problems
on which final result depends on. Then write down formulas to
solve the small problems. Use the results of this small solved
cells to final result by adopting calculations and logical
checking on these intermediate result cells. You will believe
and make yourself confident of solving the complex problems. |
|
Here is one formula text |
|
=IF(D15<=TBL,((TW*2+(TBL-D15)*(usslope1+dsslope1))/2*(TBL-D15)),0)+IF(D15<BRL2,((usberm2+dsberm2)*2+(BRL2-D15)*(usslope2-usslope1+dsslope2-dsslope1))/2*(BRL2-D15),0)+IF(D15<BRL3,((usberm3+dsberm3)*2+(BRL3-D15)*(usslope3-usslope2+dsslope3-dsslope2))/2*(BRL3-D15),0)+IF(D15<BRL4,((usberm4+dsberm4)*2+(BRL4-D15)*(usslope4-usslope3+dsslope4-dsslope3))/2*(BRL4-D15),0)+IF(D15<BRL5,((usberm5+dsberm5)*2+(BRL5-D15)*(usslope5-usslope4+dsslope5-dsslope4))/2*(BRL5-D15),0)+IF(D15<BRL6,((usberm6+dsberm6)*2+(BRL6-D15)*(usslope6-usslope5+dsslope6-dsslope5))/2*(BRL6-D15),0) |
|
This complex seen formula can broken into parts as below |
|
IF(D15<=TBL,((TW*2+(TBL-D15)*(usslope1+dsslope1))/2*(TBL-D15)),0) |
|
IF(D15<BRL2,((usberm2+dsberm2)*2+(BRL2-D15)*(usslope2-usslope1+dsslope2-dsslope1))/2*(BRL2-D15),0) |
|
IF(D15<BRL3,((usberm3+dsberm3)*2+(BRL3-D15)*(usslope3-usslope2+dsslope3-dsslope2))/2*(BRL3-D15),0) |
|
IF(D15<BRL4,((usberm4+dsberm4)*2+(BRL4-D15)*(usslope4-usslope3+dsslope4-dsslope3))/2*(BRL4-D15),0) |
|
IF(D15<BRL4,((usberm4+dsberm4)*2+(BRL4-D15)*(usslope4-usslope3+dsslope4-dsslope3))/2*(BRL4-D15),0) |
|
IF(D15<BRL5,((usberm5+dsberm5)*2+(BRL5-D15)*(usslope5-usslope4+dsslope5-dsslope4))/2*(BRL5-D15),0) |
|
IF(D15<BRL6,((usberm6+dsberm6)*2+(BRL6-D15)*(usslope6-usslope5+dsslope6-dsslope5))/2*(BRL6-D15),0) |
|
|
The above formula calculates area of Earthwork with fixed top
Level (TBL) and Changing Ground levels at different spots that
are entered in cell D15, all other seen names are defined for
constant values for the section of Earthen dam which are
constant for all positions on ground. You can evaluate each of
the modular formula easily and test make corrections if any to
it easily, that cannot be done or evaluated in big formula as
seen above. |
|
Using names in excel formulas becomes
very useful as you cannot remind each and every cell address to
be referred in such complex formulas. |
|
You later combine all text of the formulas in different cells to
single cells or hide the unnecessary columns from printing. |
|
|
|
Email US to get in touch with
us for your requirement. |
