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

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

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

Marc T wrote:
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


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

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

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


"Marc T" wrote in message
...
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

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

Marc -

Try this one:

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

--
Daryl S


"Marc T" wrote:

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

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

Marc -

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

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

--
Daryl S


"Marc T" wrote:

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

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

Thanks hugely Daryl, it worked a treat!

Marc

"Daryl S" wrote:

Marc -

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

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

--
Daryl S


"Marc T" wrote:

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

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

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

Marc T wrote:
Thanks hugely Daryl, it worked a treat!

Marc

"Daryl S" wrote:

Marc -

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

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

--
Daryl S


"Marc T" wrote:

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

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

Thanks Glenn -

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


"Glenn" wrote:

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

Marc T wrote:
Thanks hugely Daryl, it worked a treat!

Marc

"Daryl S" wrote:

Marc -

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

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

--
Daryl S


"Marc T" wrote:

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
.