View Full Version : Excel help please??
Twodogs
13th June 2006, 10:43 AM
Good Morning,
I am trying something new and I am unsure it can be be done but worth asking all the same.
What I have is 3 columns with numbers from 0 to 24. What I want to do is in a fourth column have columns 1 to 3 added together and devided by 3 to get a average. But when one of the columns has a 0 I don't want to include it in the calculation. So if there are only two columns with numbers 1 to 24 I would like to only devide by 2 rather than 3. Hope that makes sense. Can it be done??
Thanks
Twodogs
jfc
13th June 2006, 11:39 AM
=sum(A1:C1)/countif(A1:C1, ">0")
Twodogs
13th June 2006, 12:03 PM
Thanks JFC
Works a treat!! You beauty
Twodogs
jfc
13th June 2006, 12:33 PM
Thanks JFC
Works a treat!! You beauty
Twodogs
=TwoDogs/Two
Another handy tip for circumventing the evil 3-letter word "***".
Twodogs
13th June 2006, 03:31 PM
Following on the same track as above still 3 columns but I want to only add the 2 highest columns together then average them. Can that be done easily? Obviously there still could be a 0 in one or more columns.
Twodogs
jfc
13th June 2006, 03:40 PM
Following on the same track as above still 3 columns but I want to only add the 2 highest columns together then average them. Can that be done easily? Obviously there still could be a 0 in one or more columns.
Twodogs
=sum(a1:c1)-min(a1:c1)
or
=max(a1:c1)+median(a1:c1)
Twodogs
13th June 2006, 04:23 PM
Sorry JFC
It doesn't work. Problem is I can have just one column with a figure above 0 and the other two are 0. The example below is the sort of data I have in the three columns. Just want add the two highest columns and devide by 2.
46, 0, 0
48,46,42
54,42,36
Twodogs
jfc
13th June 2006, 04:40 PM
Sorry JFC
It doesn't work. Problem is I can have just one column with a figure above 0 and the other two are 0. The example below is the sort of data I have in the three columns. Just want add the two highest columns and devide by 2.
46, 0, 0
48,46,42
54,42,36
Twodogs
=if( median(a1:c1)>0,(sum(a1:c1)-min(a1:c1))/2,max(a1:c1))
Try asking the right question.
Is this your school homework?
Twodogs
13th June 2006, 04:47 PM
Thanks JFC
Sorry I will put my old head in the correct gear when I ask questions in the future as I do understand if you don't make it clear what you need it can be very hard to help out. I apprecate your time today JFC.
Twodogs
vBulletin v3.0.3, Copyright ©2000-2024, Jelsoft Enterprises Ltd.