Total of all odd cells minus total of all even cells

simontwn

New Member
Joined
May 14, 2002
Messages
29
There is a number in each cell from A1, B1, C1 to Y1. In Z1, I need a formula to equal to the Total of (A1+C1+E1...) minus (B1+D1+F1....). Thank you very much

Simon in Monterey
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hi Simon

Try:

Code:
=SUMPRODUCT(--(MOD(ROW(1:25),2)=1),TRANSPOSE(A1:Y1))-SUMPRODUCT(--(MOD(ROW(1:25),2)=0),TRANSPOSE(A1:Y1))

which must be array-entered (Ctrl+Shift+Enter) - Excel will surround with {} braces if it has worked.

Best regards

Richard
 
Upvote 0
Try this, although not very dynamic or robust,

=SUMPRODUCT(MOD(COLUMN(A1:X1),2),A1:X1)-SUMPRODUCT(MOD(COLUMN(B1:W1),2),B1:W1)

Hey Richard..... :LOL:
 
Upvote 0
Hey Brian :)

How stupid do I sound: I was struggling to figure out how to get numbers out of the COLUMN function - COLUMN(1:25) just didn't work!!!

D'Oh!!!
 
Upvote 0
I have tried both

Richard & Brian,

I tried yours both. Richard's shows "$Value!" and Brian's shows the number in cell A1. Maybe I did not copy/paste properly. I will keep on trying. Thank you two.

Simon
 
Upvote 0
Z1:

=SUMPRODUCT(--(MOD(COLUMN(A1:Y1)-COLUMN(A1)+0,2)=0),A1:Y1)-SUMPRODUCT(--(MOD(COLUMN(A1:Y1)-COLUMN(A1)+1,2)=0),A1:Y1)
 
Upvote 0
may i ask one out-of-topic question:

what is the function of "--" in excel formulas?

Regards... OP
 
Upvote 0
Simon

I tried yours both. Richard's shows "$Value!"

I appreciate Aladin's formula works as required and is simpler than the one I posted, but mine should work. It will however return #VALUE if you don't array enter (ie instead of simply pressing Enter once typed in, you need to hold down Ctrl and Shift and then, whilst holding these down, press Enter). Excel will surround the formula with {} braces if it has worked.

Richard
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top