offset and Sum function

[yshridhar]

I maintain my school data in excel. Column A is the ID nos of the students.
Column M contians respective Fee amounts. In columns y, z, aa, ab, ac We
maintian the sibling's IDs. We calculate the total amount of all the
siblings to pay in a column. The following is the formula we are using
IF(LEN($Y3)>1,SUM(OFFSET($M$1,MATCH($Y3,$A$2:$A$948,0),0,1,1))+SUM(OFFSET($M$1,MATCH($Z3,$A$2:$A$948,0),0,1,1))+SUM(OFFSET($M$1,MATCH($AA3,$A$2:$A$948,0),0,1,1))+SUM(OFFSET($M$1,MATCH($AB3,$A$2:$A$948,0),0,1,1))+SUM(OFFSET($M$1,MATCH($AC3,$A$2:$A$948,0),0,1,1)),0)
It works well. Can anyone please suggest me a simple procedure for it

 

[Lars-Åke Aspelin]

I maintain my school data in excel. Column A is the ID nos of the students.
Column M contians respective Fee amounts. In columns y, z, aa, ab, ac We
maintian the sibling's IDs. We calculate the total amount of all the
siblings to pay in a column. The following is the formula we are using
IF(LEN($Y3)>1,SUM(OFFSET($M$1,MATCH($Y3,$A$2:$A$948,0),0,1,1))+SUM(OFFSET($M$1,MATCH($Z3,$A$2:$A$948,0),0,1,1))+SUM(OFFSET($M$1,MATCH($AA3,$A$2:$A$948,0),0,1,1))+SUM(OFFSET($M$1,MATCH($AB3,$A$2:$A$948,0),0,1,1))+SUM(OFFSET($M$1,MATCH($AC3,$A$2:$A$948,0),0,1,1)),0)
It works well. Can anyone please suggest me a simple procedure for it

Sorry, I don't have any simple procedure to suggest.
However, you can shorten your formula a bit by removing the ",1,1"
at the end of each of the five OFFSET's.
And you can write SUM(OFFSET(...), OFFSET(...), ...) instead of
SUM(OFFSET(...))+SUM(OFFSET(...))+...

These two changes reduces the formula from 265 to 225 characters.

Perhaps someone else can come up with a real simple procedure.

/ Lars-Åke

 

[Roger Govier]

Hi

I would be inclined to enter the following formula in AD3
=IF(Y3="",0,INDEX($M:$M,MATCH(Y3,$A$2:$A$948,0)))
and copy across through AE3:AH3

The amount to be billed would then be
=M3+SUM(AD3:AH3)
as I presume the amount for the first child in the family will be held
in cell M3.

If you did want your formula all in one cell, then the following is
slightly shorter than your original, and has the advantage of not being
volatile.
=IF(LEN($Y3)>1,
INDEX($M:$M,MATCH($Y3,$A$2:$A$948,0))
+INDEX($M:$M,MATCH($Z3,$A$2:$A$948,0))
+INDEX($M:$M,MATCH($AA3,$A$2:$A$948,0))
+INDEX($M:$M,MATCH($AB3,$A$2:$A$948,0))
+INDEX($M:$M,MATCH($AC3,$A$2:$A$948,0)),0)
This would also require the addition of the value from M3 to pick up the
first sibling

 

[Sandy Mann]

Roger's formula may be better but would:

=SUMPRODUCT(N(OFFSET(OFFSET($M$1,,ROW(INDIRECT("1:5"))-1),MATCH($Y3,$A$2:$A$948,0),0,)))

work for you?

Try it on COPY of your sheet.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[D Hilberg]

This is optimized for shortness:

=M3 + SUMPRODUCT(Fee, MMULT(--(ID=Y3:AC3),{1;1;1;1;1;1}))

Using your actual ranges:

=M3 + SUMPRODUCT(M$3:M$948, MMULT(--(A$3:A$948=Y3:AC3),{1;1;1;1;1;1}))

Notice there must be as many 1's in the last array as columns in the
Sibling fields, and the 1's are separated by semicolons. The "--"
converts calculated True/False values to 1's and 0's. The other
suggested formulas might refresh faster.

- David Hilberg

 

[D Hilberg]

This is optimized for shortness:

=M3 + SUMPRODUCT(Fee, MMULT(--(ID=Y3:AC3),{1;1;1;1;1}))

Using your actual ranges:

=M3 + SUMPRODUCT(M$3:M$948, MMULT(--(A$3:A$948=Y3:AC3),{1;1;1;1;1}))

Notice there must be as many 1's in the last array as columns in the
Sibling fields, and the 1's are separated by semicolons. The "--"
converts calculated True/False values to 1's and 0's. The other
suggested formulas might refresh faster.

- David Hilberg

[replacing earlier post with erroneous number of 1's]

 

[yshridhar]

Thank You Mr. Roger. Can you tell me which one is faster in execution either
the formula you sent or the offset which i am using. Why offset is volatile.
Can you please explain
With regards
Sridhar

 

[Bernd P]

Hello,

With Sandy's formula I could not get correct results.

FastExcel showed in my test environment for:
Roger 27.04 msec
Sridhar 27.39 msec
David 2113.87 msec

Volatile means recalculation with each F9 even if input does not
change (should be avoided).

Regards,
Bernd

 

[yshridhar]

Thank you Mr. Bernd. Is there any special procedure to determine the
execution time of a function. Can you please explain me
With regards
Sridhar