Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with a formula
I am looking for a formula that will compute an average of a number of non
contiguous cells such as G8, G16, G24, G36, etc. Each of these cells has a formula which computes an average of a range of cells. With the helpm of this forum, I have been able to find a formula which does that AND uses values only when they are greater than zero and does not display #DIV/0!. But I cannot fin a fromula that will do the next step- Take an average of those specific cells AND use only the ones where the cell is 0, Example G8=100, G16=85, G24 is blank, G36=75, then this formula would calculate the average 100+85+75/3. All help greatly appreciated -- dbconn |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with a formula
On Sat, 23 Jan 2010 15:55:01 -0800, dbconn
wrote: I am looking for a formula that will compute an average of a number of non contiguous cells such as G8, G16, G24, G36, etc. Each of these cells has a formula which computes an average of a range of cells. With the helpm of this forum, I have been able to find a formula which does that AND uses values only when they are greater than zero and does not display #DIV/0!. But I cannot fin a fromula that will do the next step- Take an average of those specific cells AND use only the ones where the cell is 0, Example G8=100, G16=85, G24 is blank, G36=75, then this formula would calculate the average 100+85+75/3. All help greatly appreciated For you example, try this formula: =SUM(G8,G16,G24,G36)/SUM(G80,G160,G240,G360) If your data is more regular, say every 8th row, ie G32 rather than G36, you may try something like this: =SUMPRODUCT((G8:G32)*(MOD(ROW(G8:G32),8)=0))/SUMPRODUCT((G8:G320)*(MOD(ROW(G8:G32),8)=0)) This is a longer formula if you only have a few data, but if you have data in every 8th row from cell G8 to say cell G400, then this is shorter. Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with a formula
I hope that you want (100+85+75)/3 rather than 100+85+75/3 ?
If G8=100, G16=85, G24 is blank, G36=75, the formula =AVERAGE(G8, G16, G24, G36) will give (100+85+75)/3, and you can avoid a #DIV/0! error by using =IF(COUNT(G8, G16, G24, G36)=0,"",AVERAGE(G8, G16, G24, G36)) You say: "I have been able to find a formula which does that AND uses values only when they are greater than zero and does not display #DIV/0!. " so it might have been handy if you told us what that formula is, and how that fails to meet your requirement of: "Take an average of those specific cells AND use only the ones where the cell is 0". Are you really trying to exclude values <=0, or merely trying to exclude blank cells [or text strings such as "" which your other formulae may be returning] (which the AVERAGE function does anyway)? -- David Biddulph "dbconn" wrote in message ... I am looking for a formula that will compute an average of a number of non contiguous cells such as G8, G16, G24, G36, etc. Each of these cells has a formula which computes an average of a range of cells. With the helpm of this forum, I have been able to find a formula which does that AND uses values only when they are greater than zero and does not display #DIV/0!. But I cannot fin a fromula that will do the next step- Take an average of those specific cells AND use only the ones where the cell is 0, Example G8=100, G16=85, G24 is blank, G36=75, then this formula would calculate the average 100+85+75/3. All help greatly appreciated -- dbconn |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with a formula
The formula that I have created that works uses a range of cells like G8:G18.
When i try the same formula for the random cells, it does not work. This will work. Thanks much -- dbconn "David Biddulph" wrote: I hope that you want (100+85+75)/3 rather than 100+85+75/3 ? If G8=100, G16=85, G24 is blank, G36=75, the formula =AVERAGE(G8, G16, G24, G36) will give (100+85+75)/3, and you can avoid a #DIV/0! error by using =IF(COUNT(G8, G16, G24, G36)=0,"",AVERAGE(G8, G16, G24, G36)) You say: "I have been able to find a formula which does that AND uses values only when they are greater than zero and does not display #DIV/0!. " so it might have been handy if you told us what that formula is, and how that fails to meet your requirement of: "Take an average of those specific cells AND use only the ones where the cell is 0". Are you really trying to exclude values <=0, or merely trying to exclude blank cells [or text strings such as "" which your other formulae may be returning] (which the AVERAGE function does anyway)? -- David Biddulph "dbconn" wrote in message ... I am looking for a formula that will compute an average of a number of non contiguous cells such as G8, G16, G24, G36, etc. Each of these cells has a formula which computes an average of a range of cells. With the helpm of this forum, I have been able to find a formula which does that AND uses values only when they are greater than zero and does not display #DIV/0!. But I cannot fin a fromula that will do the next step- Take an average of those specific cells AND use only the ones where the cell is 0, Example G8=100, G16=85, G24 is blank, G36=75, then this formula would calculate the average 100+85+75/3. All help greatly appreciated -- dbconn . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with a formula
Thanks so much
-- dbconn "Lars-Ã…ke Aspelin" wrote: On Sat, 23 Jan 2010 15:55:01 -0800, dbconn wrote: I am looking for a formula that will compute an average of a number of non contiguous cells such as G8, G16, G24, G36, etc. Each of these cells has a formula which computes an average of a range of cells. With the helpm of this forum, I have been able to find a formula which does that AND uses values only when they are greater than zero and does not display #DIV/0!. But I cannot fin a fromula that will do the next step- Take an average of those specific cells AND use only the ones where the cell is 0, Example G8=100, G16=85, G24 is blank, G36=75, then this formula would calculate the average 100+85+75/3. All help greatly appreciated For you example, try this formula: =SUM(G8,G16,G24,G36)/SUM(G80,G160,G240,G360) If your data is more regular, say every 8th row, ie G32 rather than G36, you may try something like this: =SUMPRODUCT((G8:G32)*(MOD(ROW(G8:G32),8)=0))/SUMPRODUCT((G8:G320)*(MOD(ROW(G8:G32),8)=0)) This is a longer formula if you only have a few data, but if you have data in every 8th row from cell G8 to say cell G400, then this is shorter. Hope this helps / Lars-Ã…ke . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|