Help simplifying a formula with multiple if's added together...

  • Thread starter Thread starter Marc T
  • Start date Start date
M

Marc T

Hi All,

I have the following formula to show progress of an item at a particular date:

=IF($D329<>"",IF($D329<=V$3,($D$341/100)*$S329),0)+IF($E329<>"",IF($E329<=V$3,($E$341/100)*$S329),0)+IF($F329<>"",IF($F329<=V$3,($F$341/100)*$S329),0)+IF($G329<>"",IF($G329<=V$3,($G$341/100)*$S329),0)+IF($H329<>"",IF($H329<=V$3,($H$341/100)*$S329),0)+IF($I329<>"",IF($I329<=V$3,($I$341/100)*$S329),0)+IF($J329<>"",IF($J329<=V$3,($J$341/100)*$S329),0)+IF($K329<>"",IF($K329<=V$3,($K$341/100)*$S329),0)

V3 is the current date, S329 is the budgeted hours, D329:K329 are
deliverable dates, and D341:K341 are progress percentages...

Basically it's adding the values in the progress percentages in D341:D341 if
the corresponding date with D329:K329 is before the date in V3 and then
multiplying by the budget figure.

Is there any obvious way to simplify? Maybe an array formula?

Cheers as ever!
Marc
 
Marc said:
Hi All,

I have the following formula to show progress of an item at a particular date:

=IF($D329<>"",IF($D329<=V$3,($D$341/100)*$S329),0)+IF($E329<>"",IF($E329<=V$3,($E$341/100)*$S329),0)+IF($F329<>"",IF($F329<=V$3,($F$341/100)*$S329),0)+IF($G329<>"",IF($G329<=V$3,($G$341/100)*$S329),0)+IF($H329<>"",IF($H329<=V$3,($H$341/100)*$S329),0)+IF($I329<>"",IF($I329<=V$3,($I$341/100)*$S329),0)+IF($J329<>"",IF($J329<=V$3,($J$341/100)*$S329),0)+IF($K329<>"",IF($K329<=V$3,($K$341/100)*$S329),0)

V3 is the current date, S329 is the budgeted hours, D329:K329 are
deliverable dates, and D341:K341 are progress percentages...

Basically it's adding the values in the progress percentages in D341:D341 if
the corresponding date with D329:K329 is before the date in V3 and then
multiplying by the budget figure.

Is there any obvious way to simplify? Maybe an array formula?

Cheers as ever!
Marc
Click to expand...


=SUMPRODUCT(($D329:$K329<=V$3)*($D329:$K329<>"")*($D$341:$K$341))/100*$S329
 
I must admit to having trouble reading this formula.
Let's write the syntax of IF as:
=IF(condition, true_value, false_value)
As I read the formula, there are no false_values

That being said, here are some ideas.
We can often drop the IF when math is involved.
Example IF($D329<=V$3,($D$341/100)*$S329),0)
can be replaced by ($D329<=V$3)*(($D$341/100)*$S329)
This fives the quantity ($D$341/100)*$S329) when the comparison $D329<=V$3
is TRUE, otherwise it gives 0.

We can get rid of some parentheses
Replace ($D$341/100)*$S329) by either $D$341/100*$S329 or
$D$341*$S329/100 (shows the math better)

So IF($D329<=V$3,($D$341/100)*$S329),0) becomes
($D329<=V$3)*($D$341*$S329/100)

Of course if D341 held a percentage value like 50% not 50, then we could get
rid of the 100 in the formula.

best wishes
--
Bernard Liengme
Microsoft Excel MVP
people.stfx.ca/bliengme
email address: remove uppercase characters


news:[email protected]...
 
Marc -

Try this one (other post missed the --):

=SUMPRODUCT(--($D329:$K329 <= $V$3),$D$341:$K$341)/100*$S$329
 
Thanks hugely Daryl, it worked a treat!

Marc

Daryl S said:
Marc -

Try this one (other post missed the --):

=SUMPRODUCT(--($D329:$K329 <= $V$3),$D$341:$K$341)/100*$S$329
Click to expand...
 
Not if there are blanks in D329:K329. Your previous formula specifically
checked for them, and so did my original response:

=SUMPRODUCT(($D329:$K329<=V$3)*($D329:$K329<>"")*($D$341:$K$341))/100*$S329
 
Back
Top