LinkBack URL
About LinkBacks
The title probable isnt the best discription but i didnt know how to explain it,
what i want to due is add 3 cells and divide by 3, but if only 2 of the cells had a value then only divide by 2
=SUM(G8+G13+G18)/3
The title probable isnt the best discription but i didnt know how to explain it,
what i want to due is add 3 cells and divide by 3, but if only 2 of the cells had a value then only divide by 2
=SUM(G8+G13+G18)/3
SUM(G8+G13+G18)/Count(G8,G13,G18) count will ignore empty cells. If theres a 0 in a cell it will include it, if you want zero values ignored then we can create a countif of the same cells, and make the criteria ">"&0
Windows 7 using Office 2007 & 2010
Remember your [ code ] [ /code ] tags, makes reading soooo much easier
Hi,
To directly answer your question, it would be:
Which could also be written as:
=SUM(G8,G13,G18)/COUNT(G8,G13,G18)
=AVERAGE(G8,G13,G18)
how do you separate the numbers without using the comma as on a countif the first comma changes the syntax from range to critieria
Actually you can't use countif, it's with a non contiguous range. If your needing a >0 solution please say, i'm sure we can come up with one
yes i do as the cells will always show 0 so will always divide by 3
If you want to ignore zeros then you could use this:
=(G8+G13+G18)/((G8<>0)+(G13<>0)+(G18<>0))
THe cells I've used are E25,E27 & E29 and this works. Slightly longer formula than I thought but does the trick
SUM(E25,E27,E29)/COUNT(IF(E25=0,"",E25),+COUNT(IF(E27=0,"",E29),+COUNT(IF(E29=0,"",E29))))
Edit: this is returning a wrong value, ignore
Last edited by scottylad2; 03-06-2011 at 07:57 PM.
=SUM(E25,E27,E29)/COUNT(IF(E25>0,E25,""),IF(E27>0,E27,""),IF(E29>0,E29,""))
tested and working
Well done Scotty your a star thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks