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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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:
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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!!!
 

simontwn

New Member
Joined
May 14, 2002
Messages
29
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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)
 

opinsky

New Member
Joined
Sep 26, 2006
Messages
21
may i ask one out-of-topic question:

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

Regards... OP
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

Forum statistics

Threads
1,171,178
Messages
5,874,185
Members
433,033
Latest member
thatmacroguy

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
Top