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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
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.
 
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.
 
Thank you so much. I got it to work perfectly.

Biff said:
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
Click to expand...
 
Thank you so much. I got it to work perfectly.

Biff said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
If Hours >0, If Volume <= 0,(and vice-versa) then.... 4
Excluding Cells From Avg Cals Due to Info in different col 6
function returning SUM of 2 adjacent cols in 2 separate cells 7
SUMPRODUCT Returning 0 or wrong totals 11
Pick of random ID's 3
Possible getpivotdata with offset? 1
Totaling columns 4

Back
Top