Software Generators

Could you please provide a sample of your workbook?

Thanks!

Use Evaluate Formula toll from excel option*Ribbon Formulas--Formula Auditing.
It will take you step by step on what's going on in the calcualtion

I am actually a Mac user and I have Excel for Mac 2011, latest version. Sadly "Evaluate Formula" functionality does not exist in Excel for Mac. At work, all computers are windows based but don't want to take the risk of opening this sheet at work. Also, I am simply hoping if somebody could walk me through it so that it would help me in building such formulas in future rather than just simply copy pasting them. Thanks.

For the formula you provided in your thread here is the break down from what I gather.

Entire Formula:

=IF((M$2<=($F6+30)),IF((M$2<$F6),(IF(M$2>$E6,(((M$2-$E6))*$D6/$H6)-(SUM($I6:L6)))),(IF(M$2>$E6,((($F6-$E6))*$D6/$H6)-(SUM($I6:L6))))),0)

Break Down

1) Original test (if m2 is less than or equal to the sum of the value in F6 and 30 then…)

=IF((M$2<=($F6+30)),

2) Value if original test is true (second test) (if the original test is true then if the value in M2 is less than the value in F6 then..)

IF((M$2<$F6),

3) Value is second test is false (because you did not specify the result if false)

0

4) Value if second test is true (third test) (if the value of the second test is true then if the value in M2 is greater than the value in E6 then…)

(IF(M$2>$E6,

5) Value if third test is false (if the value is false then 0 because you did not specify what to return if false)

0

6) If the third test is true if then the value of the cell is equal to the value of (the value of M2 minus the value of e6 times d6) minus (the value of the sum of cells I6 through L6)

(((M$2-$E6))*$D6/$H6)-(SUM($I6:L6)))),

7) Value if third test is false (fourth test) ( if the value in m2 is
greater than e6 then…)

(IF(M$2>$E6,

8) Value if fourth test if true (the value of f6 minus E6 times the value
of d6 divided by h6 minus the value of the sum of i6 through L6)

((($F6-$E6))*$D6/$H6)-(SUM($I6:L6))))),

9) Value if original test and if fourth test is false

0)

@rvasquez Thanks so much that really helps me in figuring it out. If you would have also downloaded the sheet. You would see that the same logical formula has been used in other cells too for "auto distribution" When developing these kind of formulas is it possible to simply drag them to the cell you want to apply in other cells too as is case with other formulas?

You would see that at the end there's a validation column. Is that column for the validation of this formula? How does that work?

Sorry for too many questions but thanks for your kind help.

To use the formula you should just be able to copy the cell that contains the formula you want and paste it into the cell you want the formula to apply to. It's wise to make sure before copying that anything that you do not want to changed you place a $ before the letter and before the number. I believe the formulas you have right now already have done this.

I'm not quite sure what the Validation column is used for. I can tell you that it sums the values from the row from columns O through AP. Then that value is use in the cell in column AR where the value in the Scope of Work (RMT) column is subtracted from value in AQ (Validation) column.

@ rvasquez Thanks so much for your help. I think, I understand the logic now.

Hello pil,

Attached is a word document with a little more explanation.

Thanks!