Tidy a formula

[zurg]

Hello all

I was hoping someone could look at the below and advise how to 'tidy'. Ideally I'd like to copy the formula down but I'm not sure how I would do this in it's current form.

I am trying to calculate how much I will be charged. I am charged £2/ week for each pallet, however the number of pallets I will use will go down equally over the course of time.

AA column contains how many weeks I will have the number of pallets in E2.
AB column contains my charge per pallet per week (£2).
E column contains the number of pallets (total) I have.

With the sample below, I have:

(AA2*AB2*E2)+(AA2*AB2*(E2-1))+(AA2*AB2*(E2-2))+(AA2*AB2*(E2-3))+(AA2*AB2*(E2-4))+(AA2*AB2*(E2-5))+(AA2*AB2*(E2-6)).

AA2 weeks I will be charged £2 multiplied by E2 pallets.
PLUS
AA2 weeks I will be charged £2 multiplied by E2-1 pallet.

and so on.

There must be a much better way of doing this! I am currently manually adjusting the E2-3 but there must be a way I can do this using FACT or similar.

Any help will be greatly appreciated!

Thank you so much in advance!

Joanna

[Tony Valko]

Maybe this array formula**:

=SUM(AA2*AB2*(E2-{1;2;3;4;5;6}))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

If that doesn't work then tell us what's in the cells and what result you expect.

[JohnTopley]

Try

=AA2*AB2*SUM(OFFSET($E$2,0,0,E2,1))

[kvsrinivasamurthy]

Try this
=SUMPRODUCT(AA2*AB2*(E2-ROW($1:$6)))