# Thread: Formula query (97)

1. ## Formula query (97)

Hi all,

Could anyone please tell me how to do the following. I have a large spreadsheet where I want to deduct the kilo's in row a2 from row a1 and then row a4 from row a3 ect. In other words I want the formula copied to every 2nd row to enable me to work out an average.

Thanks

2. ## Re: Formula query (97)

This array formula seems to do it all in one cell:

=AVERAGE(IF(MOD(ROW(A2:A100),2)>0,"",A2:A100-A1:A99))

(press control-shift-enter when done editing the formula to tell XL it is an array formula)

3. ## Re: Formula query (97)

Baie dankie, Thank you very much for the reply. Is it possible to explain the formula so I can actually understand what I am doing.

Thanks again

4. ## Re: Formula query (97)

Explanation.

=AVERAGE(IF(MOD(ROW(A2:A100),2)>0,"",A2:A100-A1:A99))

Lets do this for just rows 2-5.

ROW(A2:A5) yields this array:
{2,3,4,5}

MOD({2,3,4,5},2) yields:
{0,1,0,1}

Thus
IF({0,1,0}>0,Trueclause, Falseclause)
yields
{Falseclause, Trueclause, Falseclause, Trueclause}

So the end result:

Average({"", A3-A2,"",A5-A4})

Clear as mud?

5. ## Re: Formula query (97)

Excellent stuff, thank you Jan Karel

6. ## Re: Formula query (97)

I assume you mean cell A1, A2 etc.

In cell B2 put the formula =A1-A2.
Select cells B1and B2.
Put the cursor in the bottom right corner of B2 to bring up the fill handle, then double click it.

In Col B you now have alternating blanks and formulas.

7. ## Re: Formula query (97)

Hey, I liked my complicated solution better ! <vbg>

8. ## Re: Formula query (97)

Thanks to both of you. It's nice to learn something new and usefull from you gurus.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•