I need to total col, but pick up number in other col. if not 0
 

I have two columns of figures i.e. Col. G has figures in each cell and have
totalled them and averaged them. In Col. F most of the cells have 0, but if
a cell is populated with a figure other than 0, then I need to use that
figure instead of the figure in Row G.

I need to total col, but pick up number in other col. if not 0
 

If I understand........

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=SUM(IF(F1:F10<0,F1:F10,G1:G10))

=AVERAGE(IF(F1:F10<0,F1:F10,G1:G10))

Biff

"katie" wrote in message
...
I have two columns of figures i.e. Col. G has figures in each cell and have
totalled them and averaged them. In Col. F most of the cells have 0, but
if
a cell is populated with a figure other than 0, then I need to use that
figure instead of the figure in Row G.

I need to total col, but pick up number in other col. if not 0
 

=IF(F1<0,F1,G1)

If F1 is less than or greater than 0, F1, else G1.

Dave
--
Brevity is the soul of wit.


"katie" wrote:

I have two columns of figures i.e. Col. G has figures in each cell and have
totalled them and averaged them. In Col. F most of the cells have 0, but if
a cell is populated with a figure other than 0, then I need to use that
figure instead of the figure in Row G.

I need to total col, but pick up number in other col. if not 0
 

I'd make another column and then put a formula like this in it:
=IF(F1=0,G1,F1) and copy it down the length of your data, it will look at F,
if it is a zero it will put the value of G in the cell, if not it will put
the value of F in the cell, you can then total and average that column.

"katie" wrote:

I have two columns of figures i.e. Col. G has figures in each cell and have
totalled them and averaged them. In Col. F most of the cells have 0, but if
a cell is populated with a figure other than 0, then I need to use that
figure instead of the figure in Row G.

I need to total col, but pick up number in other col. if not
 

Thank you so much. I got it to work perfectly.

"Biff" wrote:

If I understand........

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=SUM(IF(F1:F10<0,F1:F10,G1:G10))

=AVERAGE(IF(F1:F10<0,F1:F10,G1:G10))

Biff

"katie" wrote in message
...
I have two columns of figures i.e. Col. G has figures in each cell and have
totalled them and averaged them. In Col. F most of the cells have 0, but
if
a cell is populated with a figure other than 0, then I need to use that
figure instead of the figure in Row G.

I need to total col, but pick up number in other col. if not
 

You're welcome. Thanks for the feedback!

Biff

"katie" wrote in message
...
Thank you so much. I got it to work perfectly.

"Biff" wrote:

If I understand........

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=SUM(IF(F1:F10<0,F1:F10,G1:G10))

=AVERAGE(IF(F1:F10<0,F1:F10,G1:G10))

Biff

"katie" wrote in message
...
I have two columns of figures i.e. Col. G has figures in each cell and
have
totalled them and averaged them. In Col. F most of the cells have 0,
but
if
a cell is populated with a figure other than 0, then I need to use that
figure instead of the figure in Row G.