|
Help with excel formula
I know wrong newsgroup but trying to get something I can understand So
hoping one of you guys understands excel formulas. Using this formula: =AVERAGEIF(C3:AL3,"%",C18:AL18) What thats doing for me is giving me the number of cells that have a number in them under the column heading % and that works fine. Problem is if the cells in C18:AL18 are zero it picks up the zero and counts that in as well. Even if I change the cells to dont show a zero show a blank it is effectively still there. Can anyone help to adjust the formula for me. This is the answer in english but how the heck do I do it. ".....If you use formulas to calculate the percentage column, return a blank instead of a zero to have it ignored in the average...." So this needs to change: =IFERROR(G18/F18*(100),"") |
Help with excel formula
|
Help with excel formula
On 06/05/2015 16:02, JimK wrote:
http://www.excelfunctions.net/Excel-...-Function.html Thanks Jim although I still cant work it out from that. I need to add something to my formula so that a blank cell does not contain an invisible zero which is then picked up and used to count my averages. Excuse my terminology but not too clever with excel stuff. |
Help with excel formula
On 06/05/2015 16:24, ss wrote:
On 06/05/2015 16:02, JimK wrote: http://www.excelfunctions.net/Excel-...-Function.html Thanks Jim although I still cant work it out from that. I need to add something to my formula so that a blank cell does not contain an invisible zero which is then picked up and used to count my averages. Excuse my terminology but not too clever with excel stuff. You can do a countblank (or 'if ', etc), and simply deduct or add that to your subtotal. I tend to do this sort of thing using a load of clumsy equations in brackets. Works best for me as I don't use Excel that often. -- Cheers, Rob |
Help with excel formula
On 06/05/2015 16:46, RJH wrote:
On 06/05/2015 16:24, ss wrote: On 06/05/2015 16:02, JimK wrote: http://www.excelfunctions.net/Excel-...-Function.html Thanks Jim although I still cant work it out from that. I need to add something to my formula so that a blank cell does not contain an invisible zero which is then picked up and used to count my averages. Excuse my terminology but not too clever with excel stuff. You can do a countblank (or 'if ', etc), and simply deduct or add that to your subtotal. I tend to do this sort of thing using a load of clumsy equations in brackets. Works best for me as I don't use Excel that often. I'm lost, because the OP seems to be making this rather more complicated than it seems to need to be. What's wrong with a formula like this one, using the correct function for the job? =COUNTIF(B1:B19,"0") |
Help with excel formula
On 06/05/2015 15:44, ss wrote:
I know wrong newsgroup but trying to get something I can understand So hoping one of you guys understands excel formulas. Using this formula: =AVERAGEIF(C3:AL3,"%",C18:AL18) What thats doing for me is giving me the number of cells that have a number in them under the column heading % and that works fine. Problem is if the cells in C18:AL18 are zero it picks up the zero and counts that in as well. Even if I change the cells to dont show a zero show a blank it is effectively still there. Can anyone help to adjust the formula for me. This is the answer in english but how the heck do I do it. ".....If you use formulas to calculate the percentage column, return a blank instead of a zero to have it ignored in the average...." Normally when doing this kind of thing I would compute the cells you are averaging conditionally: =IF( expression 0, expression, " ") That will fill any zero results with a blank cell rather than a zero. -- Cheers, John. /================================================== ===============\ | Internode Ltd - http://www.internode.co.uk | |-----------------------------------------------------------------| | John Rumm - john(at)internode(dot)co(dot)uk | \================================================= ================/ |
Help with excel formula
On 06/05/2015 18:13, John Rumm wrote:
=IF( expression 0, expression, " ") Maybe this will help explain it better. The formula at the top should pick up under the % heading the number of cells that have an entry. As you can see only 3 of those cells have an entry which returns arrowed 58.75 which is the wrong answer. The calculation should be 100+60+75 = 235 / 3 = correct answer of 78.3 http://i64.photobucket.com/albums/h1...psql11doqn.jpg |
Help with excel formula
On Wed, 06 May 2015 17:31:35 +0100, GB wrote:
On 06/05/2015 16:46, RJH wrote: On 06/05/2015 16:24, ss wrote: On 06/05/2015 16:02, JimK wrote: http://www.excelfunctions.net/Excel-...-Function.html Thanks Jim although I still cant work it out from that. I need to add something to my formula so that a blank cell does not contain an invisible zero which is then picked up and used to count my averages. Excuse my terminology but not too clever with excel stuff. You can do a countblank (or 'if ', etc), and simply deduct or add that to your subtotal. I tend to do this sort of thing using a load of clumsy equations in brackets. Works best for me as I don't use Excel that often. I'm lost, because the OP seems to be making this rather more complicated than it seems to need to be. What's wrong with a formula like this one, using the correct function for the job? =COUNTIF(B1:B19,"0") Because that simply gives you the number of cells that are 0, rather than the avereage of the contents of cells that are 0. |
Help with excel formula
On 06/05/2015 15:44, ss wrote:
I know wrong newsgroup but trying to get something I can understand So hoping one of you guys understands excel formulas. Using this formula: =AVERAGEIF(C3:AL3,"%",C18:AL18) What thats doing for me is giving me the number of cells that have a number in them under the column heading % and that works fine. Problem is if the cells in C18:AL18 are zero it picks up the zero and counts that in as well. Even if I change the cells to dont show a zero show a blank it is effectively still there. Can anyone help to adjust the formula for me. This is the answer in english but how the heck do I do it. ".....If you use formulas to calculate the percentage column, return a blank instead of a zero to have it ignored in the average...." So this needs to change: =IFERROR(G18/F18*(100),"") The Excel newsgroups were always my first port of call. Not in the least nerdy microsoft.public.excel.worksheet.functions I'm sure you'll get an elegant solution there ....if the group still exists |
Help with excel formula
On 06/05/2015 19:09, stuart noble wrote:
On 06/05/2015 15:44, ss wrote: I know wrong newsgroup but trying to get something I can understand So hoping one of you guys understands excel formulas. Using this formula: =AVERAGEIF(C3:AL3,"%",C18:AL18) What thats doing for me is giving me the number of cells that have a number in them under the column heading % and that works fine. Problem is if the cells in C18:AL18 are zero it picks up the zero and counts that in as well. Even if I change the cells to dont show a zero show a blank it is effectively still there. Can anyone help to adjust the formula for me. This is the answer in english but how the heck do I do it. ".....If you use formulas to calculate the percentage column, return a blank instead of a zero to have it ignored in the average...." So this needs to change: =IFERROR(G18/F18*(100),"") The Excel newsgroups were always my first port of call. Not in the least nerdy microsoft.public.excel.worksheet.functions I'm sure you'll get an elegant solution there ....if the group still exists I used that a lot in the past but pretty dead these days. |
Help with excel formula
On 06/05/2015 19:00, Bob Eager wrote:
On Wed, 06 May 2015 17:31:35 +0100, GB wrote: On 06/05/2015 16:46, RJH wrote: On 06/05/2015 16:24, ss wrote: On 06/05/2015 16:02, JimK wrote: http://www.excelfunctions.net/Excel-...-Function.html Thanks Jim although I still cant work it out from that. I need to add something to my formula so that a blank cell does not contain an invisible zero which is then picked up and used to count my averages. Excuse my terminology but not too clever with excel stuff. You can do a countblank (or 'if ', etc), and simply deduct or add that to your subtotal. I tend to do this sort of thing using a load of clumsy equations in brackets. Works best for me as I don't use Excel that often. I'm lost, because the OP seems to be making this rather more complicated than it seems to need to be. What's wrong with a formula like this one, using the correct function for the job? =COUNTIF(B1:B19,"0") Because that simply gives you the number of cells that are 0, rather than the avereage of the contents of cells that are 0. But he says "What thats doing for me is giving me the number of cells that have a number in them". So, I assume he just wants to know the number of cells? The average can be done with sumif and countif, of course. It might help if the OP simply says what he wants to achieve? |
Help with excel formula
It might help if the OP simply says what he wants to achieve? I have 12 columns for months each month containing 3 columns apps/sales/% In the rpws for those columns various products. At the bottom od the % column is the % for that month. So in all I have 36 columns If for example we are 3 months gone, then I want to add the totals for each of those % columns and divide by 3. This will give me an average %. In doing this I want cells that are zero to be shown as blank With the formula I have although some cells look blank they do in fact contain a zero which the formula picks up on. A few posts down I have a link to an image of the sheet. |
Help with excel formula
On 06/05/2015 20:57, ss wrote:
It might help if the OP simply says what he wants to achieve? I have 12 columns for months each month containing 3 columns apps/sales/% In the rpws for those columns various products. At the bottom od the % column is the % for that month. So in all I have 36 columns If for example we are 3 months gone, then I want to add the totals for each of those % columns and divide by 3. This will give me an average %. In doing this I want cells that are zero to be shown as blank With the formula I have although some cells look blank they do in fact contain a zero which the formula picks up on. A few posts down I have a link to an image of the sheet. yES, IT'S CLEAR NOW WHAT YOU WANT TO DO, AND WHAT'S GOING WRONG. (Ooops caps.) What's the formula in cell Q18? Have you tried: =IF(P180,O18/P18,"") |
Help with excel formula
On 06/05/2015 21:31, GB wrote:
On 06/05/2015 20:57, ss wrote: It might help if the OP simply says what he wants to achieve? I have 12 columns for months each month containing 3 columns apps/sales/% In the rpws for those columns various products. At the bottom od the % column is the % for that month. So in all I have 36 columns If for example we are 3 months gone, then I want to add the totals for each of those % columns and divide by 3. This will give me an average %. In doing this I want cells that are zero to be shown as blank With the formula I have although some cells look blank they do in fact contain a zero which the formula picks up on. A few posts down I have a link to an image of the sheet. yES, IT'S CLEAR NOW WHAT YOU WANT TO DO, AND WHAT'S GOING WRONG. (Ooops caps.) What's the formula in cell Q18? Have you tried: =IF(P180,O18/P18,"") Sorry, the other way around is what you are doing, so =IF(O180,P18/O18,"") |
Help with excel formula
In article , ss
writes On 06/05/2015 19:09, stuart noble wrote: On 06/05/2015 15:44, ss wrote: I know wrong newsgroup but trying to get something I can understand So hoping one of you guys understands excel formulas. Using this formula: =AVERAGEIF(C3:AL3,"%",C18:AL18) What thats doing for me is giving me the number of cells that have a number in them under the column heading % and that works fine. Problem is if the cells in C18:AL18 are zero it picks up the zero and counts that in as well. Even if I change the cells to dont show a zero show a blank it is effectively still there. Can anyone help to adjust the formula for me. This is the answer in english but how the heck do I do it. ".....If you use formulas to calculate the percentage column, return a blank instead of a zero to have it ignored in the average...." So this needs to change: =IFERROR(G18/F18*(100),"") The Excel newsgroups were always my first port of call. Not in the least nerdy microsoft.public.excel.worksheet.functions I'm sure you'll get an elegant solution there ....if the group still exists I used that a lot in the past but pretty dead these days. But that's the advantage of newsgroups They can appear to be dead but people can still lurk there with no overhead. -- bert |
Help with excel formula
On Wed, 06 May 2015 20:57:22 +0100, ss wrote:
It might help if the OP simply says what he wants to achieve? I have 12 columns for months each month containing 3 columns apps/sales/% In the rpws for those columns various products. At the bottom od the % column is the % for that month. So in all I have 36 columns If for example we are 3 months gone, then I want to add the totals for each of those % columns and divide by 3. This will give me an average %. In doing this I want cells that are zero to be shown as blank With the formula I have although some cells look blank they do in fact contain a zero which the formula picks up on. A few posts down I have a link to an image of the sheet. Then isn't it just AVERAGEIF(rangem "0") ?? |
Help with excel formula
http://i64.photobucket.com/albums/h1...psql11doqn.jpg
Why does that link start a Google playstore app and take my android tablet to a game download? Jim K |
Help with excel formula
"ss" wrote in message
... On 06/05/2015 16:02, JimK wrote: http://www.excelfunctions.net/Excel-...-Function.html Thanks Jim although I still cant work it out from that. I need to add something to my formula so that a blank cell does not contain an invisible zero which is then picked up and used to count my averages. Excuse my terminology but not too clever with excel stuff. http://excelribbon.tips.net/T007729_...ro_Values.html |
Help with excel formula
On 06/05/2015 21:31, GB wrote:
On 06/05/2015 20:57, ss wrote: It might help if the OP simply says what he wants to achieve? I have 12 columns for months each month containing 3 columns apps/sales/% In the rpws for those columns various products. At the bottom od the % column is the % for that month. So in all I have 36 columns If for example we are 3 months gone, then I want to add the totals for each of those % columns and divide by 3. This will give me an average %. In doing this I want cells that are zero to be shown as blank With the formula I have although some cells look blank they do in fact contain a zero which the formula picks up on. A few posts down I have a link to an image of the sheet. yES, IT'S CLEAR NOW WHAT YOU WANT TO DO, AND WHAT'S GOING WRONG. (Ooops caps.) What's the formula in cell Q18? Have you tried: =IF(P180,O18/P18,"") Q18 =IFERROR(P18/O18*(100),"") |
Help with excel formula
On 06/05/2015 15:44, ss wrote:
I know wrong newsgroup but trying to get something I can understand So hoping one of you guys understands excel formulas. Using this formula: =AVERAGEIF(C3:AL3,"%",C18:AL18) What thats doing for me is giving me the number of cells that have a number in them under the column heading % and that works fine. Problem is if the cells in C18:AL18 are zero it picks up the zero and counts that in as well. Even if I change the cells to dont show a zero show a blank it is effectively still there. If you change the formula to =AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18,"0") it'll disregard the cells containing zero NB the format is different to AVERAGEIF because the "average range" is the first parameter, followed by pairs of "condition_range","condition" parameters. It looks like you may get 'divide by zero' errors, but you can use an IFERROR condition to control that =IFERROR(AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18," 0"),"") |
Help with excel formula
O
yES, IT'S CLEAR NOW WHAT YOU WANT TO DO, AND WHAT'S GOING WRONG. (Ooops caps.) What's the formula in cell Q18? Have you tried: =IF(P180,O18/P18,"") Sorry, the other way around is what you are doing, so =IF(O180,P18/O18,"") That still gives thr wrong answer. In the cell I require the answer I have =AVERAGEIF(C3:AL3,"%",C18:AL18) |
Help with excel formula
On 06/05/2015 22:00, ss wrote:
On 06/05/2015 21:31, GB wrote: On 06/05/2015 20:57, ss wrote: It might help if the OP simply says what he wants to achieve? I have 12 columns for months each month containing 3 columns apps/sales/% In the rpws for those columns various products. At the bottom od the % column is the % for that month. So in all I have 36 columns If for example we are 3 months gone, then I want to add the totals for each of those % columns and divide by 3. This will give me an average %. In doing this I want cells that are zero to be shown as blank With the formula I have although some cells look blank they do in fact contain a zero which the formula picks up on. A few posts down I have a link to an image of the sheet. yES, IT'S CLEAR NOW WHAT YOU WANT TO DO, AND WHAT'S GOING WRONG. (Ooops caps.) What's the formula in cell Q18? Have you tried: =IF(P180,O18/P18,"") Q18 =IFERROR(P18/O18*(100),"") If you change it to: =IF(O180,P18/O18,"") |
Help with excel formula
On 06/05/2015 21:45, JimK wrote:
http://i64.photobucket.com/albums/h1...psql11doqn.jpg Why does that link start a Google playstore app and take my android tablet to a game download? Jim K I have no idea it takes me to the image. |
Help with excel formula
On 06/05/2015 22:06, GB wrote:
On 06/05/2015 22:00, ss wrote: On 06/05/2015 21:31, GB wrote: On 06/05/2015 20:57, ss wrote: It might help if the OP simply says what he wants to achieve? I have 12 columns for months each month containing 3 columns apps/sales/% In the rpws for those columns various products. At the bottom od the % column is the % for that month. So in all I have 36 columns If for example we are 3 months gone, then I want to add the totals for each of those % columns and divide by 3. This will give me an average %. In doing this I want cells that are zero to be shown as blank With the formula I have although some cells look blank they do in fact contain a zero which the formula picks up on. A few posts down I have a link to an image of the sheet. yES, IT'S CLEAR NOW WHAT YOU WANT TO DO, AND WHAT'S GOING WRONG. (Ooops caps.) What's the formula in cell Q18? Have you tried: =IF(P180,O18/P18,"") Q18 =IFERROR(P18/O18*(100),"") If you change it to: =IF(O180,P18/O18,"") Maybe this will help (I cant really understand it) Apparently my formula is correct but for, and I quote: "The only way to get to 58.75 is if one of the % columns contains a 0. Looks like you are using formulas to calculate the percentage and format away zero values, but they are still in the cells. If you use formulas to calculate the percentage column, return a blank instead of a zero to have it ignored in the average. Just thinking maybe its the cell I want the average in that he is referring to? |
Help with excel formula
On 06/05/2015 22:01, Jim Newman wrote:
On 06/05/2015 15:44, ss wrote: I know wrong newsgroup but trying to get something I can understand So hoping one of you guys understands excel formulas. Using this formula: =AVERAGEIF(C3:AL3,"%",C18:AL18) What thats doing for me is giving me the number of cells that have a number in them under the column heading % and that works fine. Problem is if the cells in C18:AL18 are zero it picks up the zero and counts that in as well. Even if I change the cells to dont show a zero show a blank it is effectively still there. If you change the formula to =AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18,"0") it'll disregard the cells containing zero NB the format is different to AVERAGEIF because the "average range" is the first parameter, followed by pairs of "condition_range","condition" parameters. It looks like you may get 'divide by zero' errors, but you can use an IFERROR condition to control that =IFERROR(AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18," 0"),"") Thats it! spot on, problem solve. Thank you all |
Help with excel formula
On 06/05/2015 18:13, John Rumm wrote:
On 06/05/2015 15:44, ss wrote: I know wrong newsgroup but trying to get something I can understand So hoping one of you guys understands excel formulas. Using this formula: =AVERAGEIF(C3:AL3,"%",C18:AL18) What thats doing for me is giving me the number of cells that have a number in them under the column heading % and that works fine. Problem is if the cells in C18:AL18 are zero it picks up the zero and counts that in as well. Even if I change the cells to dont show a zero show a blank it is effectively still there. Can anyone help to adjust the formula for me. This is the answer in english but how the heck do I do it. ".....If you use formulas to calculate the percentage column, return a blank instead of a zero to have it ignored in the average...." Normally when doing this kind of thing I would compute the cells you are averaging conditionally: =IF( expression 0, expression, " ") That will fill any zero results with a blank cell rather than a zero. Agreed. Also, if the formulae get complicated it is often worth adding one or more intermediate columns with simpler formulae in them. Easier to understand and debug. You can always hide them when it's working properly. |
Help with excel formula
On 06/05/2015 22:20, ss wrote:
On 06/05/2015 22:01, Jim Newman wrote: On 06/05/2015 15:44, ss wrote: I know wrong newsgroup but trying to get something I can understand So hoping one of you guys understands excel formulas. Using this formula: =AVERAGEIF(C3:AL3,"%",C18:AL18) What thats doing for me is giving me the number of cells that have a number in them under the column heading % and that works fine. Problem is if the cells in C18:AL18 are zero it picks up the zero and counts that in as well. Even if I change the cells to dont show a zero show a blank it is effectively still there. If you change the formula to =AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18,"0") it'll disregard the cells containing zero NB the format is different to AVERAGEIF because the "average range" is the first parameter, followed by pairs of "condition_range","condition" parameters. It looks like you may get 'divide by zero' errors, but you can use an IFERROR condition to control that =IFERROR(AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18," 0"),"") Thats it! spot on, problem solve. Thank you all That's what I love about spreadsheet queries. There's always a solution that works and everybody moves on (unlike this place!) |
Help with excel formula
On 06/05/2015 22:20, ss wrote:
On 06/05/2015 22:01, Jim Newman wrote: On 06/05/2015 15:44, ss wrote: I know wrong newsgroup but trying to get something I can understand So hoping one of you guys understands excel formulas. Using this formula: =AVERAGEIF(C3:AL3,"%",C18:AL18) What thats doing for me is giving me the number of cells that have a number in them under the column heading % and that works fine. Problem is if the cells in C18:AL18 are zero it picks up the zero and counts that in as well. Even if I change the cells to dont show a zero show a blank it is effectively still there. If you change the formula to =AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18,"0") it'll disregard the cells containing zero NB the format is different to AVERAGEIF because the "average range" is the first parameter, followed by pairs of "condition_range","condition" parameters. It looks like you may get 'divide by zero' errors, but you can use an IFERROR condition to control that =IFERROR(AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18," 0"),"") Thats it! spot on, problem solve. Thank you all Happy to help The only suggestion I would make is that you amend it to 'fix' the check against row 3, by using =IFERROR(AVERAGEIFS(C18:AL18,$C$3:$AL$3,"%",C18:AL 18,"0"),"") as then you could set it for the first row, and then drag down to complete the rest of the column. |
| All times are GMT +1. The time now is 04:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004 - 2014 DIYbanter