Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average help???
Is there anyway I can include the value of the cell with a * but not perform
the average for that sepcific cell: for example 55,67,89*,22,37 the totals would be 270 but the average would be /4 and not /5 because of the * Thanks for any help John |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average help???
The OP replied in a different thread that he got it working.
John wrote: Is there anyway I can include the value of the cell with a * but not perform the average for that sepcific cell: for example 55,67,89*,22,37 the totals would be 270 but the average would be /4 and not /5 because of the * Thanks for any help John -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average help???
Unfortunately this is a slightly different problem and I still cannot get it
to work. "Dave Peterson" wrote: The OP replied in a different thread that he got it working. John wrote: Is there anyway I can include the value of the cell with a * but not perform the average for that sepcific cell: for example 55,67,89*,22,37 the totals would be 270 but the average would be /4 and not /5 because of the * Thanks for any help John -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average help???
In B1 you could use - drag down
=IF(ISNUMBER(A1),0,VALUE(MID(A1,1,(LEN(A1)-1)))) This checks to see if the value in A1 is a number if it is then 0, if it is a XX*, it checks the length and returns a value. To count the number of cells with a value, the divisor: =COUNTIF(A1:A5,"0") To include the value of the figure with * =SUM(A1:A5,B1:B5) This will produce the value that you are looking for. The XXX* can be of any length and can be in any row. edvwvw John wrote: Is there anyway I can include the value of the cell with a * but not perform the average for that sepcific cell: for example 55,67,89*,22,37 the totals would be 270 but the average would be /4 and not /5 because of the * Thanks for any help John -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200905/1 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average help???
If you enter
89* in a cell, then excel will treat it as text. And it won't be included in the average (or sum or count of cells with numbers). So are you typing that asterisk or is that the result of formatting? Does it represent a negative number? John wrote: Unfortunately this is a slightly different problem and I still cannot get it to work. "Dave Peterson" wrote: The OP replied in a different thread that he got it working. John wrote: Is there anyway I can include the value of the cell with a * but not perform the average for that sepcific cell: for example 55,67,89*,22,37 the totals would be 270 but the average would be /4 and not /5 because of the * Thanks for any help John -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions |