From: bb on 19 Apr 2010 23:18 i'll try to explain best i can.... i have 3 colums, first and second are put in and the third is the difference between them. then on the bottom, i have the averages. i know how to do the subtraction, but for the other rows below keep showing "0" even though and the averages are now correct. how can i keep that column to automatically show the difference without always having the zero there and not having to always put that function in that box. here is what i looks like # DATE COURSE SCORE PAR (+/-) 1 04/03/10 49 37 12 2 04/03/10 43 34 9 3 04/19/10 44 36 8 4 0 5 0 6 0 7 0 8 0 9 0 0 AVERAGE 45.33 35.67 1.38 From: FSt1 on 19 Apr 2010 23:57 hi the usual way of doing this is to use an if formula =if(C2-D2=0,"",C2-D2) copy down as far as needed. if there is no data in C or D then the cell with the if formula will show as blank. the average formulas will ignore the "blank" cells and calculate only on cells that show data. regards FSt1 "bb" wrote: > i'll try to explain best i can.... > i have 3 colums, first and second are put in and the third is the difference > between them. then on the bottom, i have the averages. i know how to do the > subtraction, but for the other rows below keep showing "0" even though and > the averages are now correct. how can i keep that column to automatically > show the difference without always having the zero there and not having to > always put that function in that box. > > here is what i looks like > > # DATE COURSE SCORE PAR (+/-) > 1 04/03/10 49 37 12 > 2 04/03/10 43 34 9 > 3 04/19/10 44 36 8 > 4 0 > 5 0 > 6 0 > 7 0 > 8 0 > 9 0 > 0 > > AVERAGE 45.33 35.67 1.38 > > From: Ron on 20 Apr 2010 02:20 Another method is to untick the show 'zero in a cell' option. Check out help in your version of Excel "bb" wrote: > i'll try to explain best i can.... > i have 3 colums, first and second are put in and the third is the difference > between them. then on the bottom, i have the averages. i know how to do the > subtraction, but for the other rows below keep showing "0" even though and > the averages are now correct. how can i keep that column to automatically > show the difference without always having the zero there and not having to > always put that function in that box. > > here is what i looks like > > # DATE COURSE SCORE PAR (+/-) > 1 04/03/10 49 37 12 > 2 04/03/10 43 34 9 > 3 04/19/10 44 36 8 > 4 0 > 5 0 > 6 0 > 7 0 > 8 0 > 9 0 > 0 > > AVERAGE 45.33 35.67 1.38 > > From: FSt1 on 20 Apr 2010 10:01 hi true but that wont stop the average formula from counting the zero in it average. regards FSt1 "Ron(a)Buy" wrote: > Another method is to untick the show 'zero in a cell' option. > Check out help in your version of Excel > > "bb" wrote: > > > i'll try to explain best i can.... > > i have 3 colums, first and second are put in and the third is the difference > > between them. then on the bottom, i have the averages. i know how to do the > > subtraction, but for the other rows below keep showing "0" even though and > > the averages are now correct. how can i keep that column to automatically > > show the difference without always having the zero there and not having to > > always put that function in that box. > > > > here is what i looks like > > > > # DATE COURSE SCORE PAR (+/-) > > 1 04/03/10 49 37 12 > > 2 04/03/10 43 34 9 > > 3 04/19/10 44 36 8 > > 4 0 > > 5 0 > > 6 0 > > 7 0 > > 8 0 > > 9 0 > > 0 > > > > AVERAGE 45.33 35.67 1.38 > > > >  |