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

[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 D341341 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

 

[Glenn]

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 D341341 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

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

 

[Bernard Liengme]

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

 

[Daryl S]

Marc -

Try this one:

=SUMPRODUCT($D329:$K329 <= $V$3,$D$341:$K$341)/100*$S$329

 

[Daryl S]

Marc -

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

=SUMPRODUCT(--($D329:$K329 <= $V$3),$D$341:$K$341)/100*$S$329

 

[Marc T]

Thanks hugely Daryl, it worked a treat!

Marc

Marc -

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

=SUMPRODUCT(--($D329:$K329 <= $V$3),$D$341:$K$341)/100*$S$329

 

[Glenn]

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

 

[Daryl S]

Thanks Glenn -

Yes, you should check for empty dates, too, as in Glenn's case...