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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

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,458
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

ADVERTISEMENT

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,209
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

ADVERTISEMENT

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,136,349
Messages
5,675,244
Members
419,557
Latest member
razlevav

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