Average Function Problem
I am using the following formula in Excel 2003
=ROUND(AVERAGE(A4:T4),1) and I need to ignore any values that contain a * can any please help?? Thanks in advance John |
Average Function Problem
John,
Any displayed values that contain a * should be text, so they should be ignored. Any cell that contains a formuls like =A1*4 will not be ignored, since they will return a value. So, what do you mean, exactly? HTH, Bernie MS Excel MVP "John" wrote in message ... I am using the following formula in Excel 2003 =ROUND(AVERAGE(A4:T4),1) and I need to ignore any values that contain a * can any please help?? Thanks in advance John |
Average Function Problem
Try this
=Round(AVERAGE(IF(A4:T40,A4:T4)),1) If this post helps click Yes --------------- Jacob Skaria "John" wrote: I am using the following formula in Excel 2003 =ROUND(AVERAGE(A4:T4),1) and I need to ignore any values that contain a * can any please help?? Thanks in advance John |
Average Function Problem
Jacob,
Unfortunately I copied and pasted your response and the error message appeared #VALUE! Thanks, John "Jacob Skaria" wrote: Try this =Round(AVERAGE(IF(A4:T40,A4:T4)),1) If this post helps click Yes --------------- Jacob Skaria "John" wrote: I am using the following formula in Excel 2003 =ROUND(AVERAGE(A4:T4),1) and I need to ignore any values that contain a * can any please help?? Thanks in advance John |
Average Function Problem
Sorry forgot to mention that this is an array fomula. Within the cell in edit
mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in the Formula Bar you can notice the curly braces at both ends "{=<formula}" -- If this post helps click Yes --------------- Jacob Skaria "John" wrote: Jacob, Unfortunately I copied and pasted your response and the error message appeared #VALUE! Thanks, John "Jacob Skaria" wrote: Try this =Round(AVERAGE(IF(A4:T40,A4:T4)),1) If this post helps click Yes --------------- Jacob Skaria "John" wrote: I am using the following formula in Excel 2003 =ROUND(AVERAGE(A4:T4),1) and I need to ignore any values that contain a * can any please help?? Thanks in advance John |
Average Function Problem
In a row I have values in cells from for example A4:T4 but on the end of some
of the value I have a * and I want the average to ignore the velues with a * Example 55,67,89*,100,10* Thanks "Bernie Deitrick" wrote: John, Any displayed values that contain a * should be text, so they should be ignored. Any cell that contains a formuls like =A1*4 will not be ignored, since they will return a value. So, what do you mean, exactly? HTH, Bernie MS Excel MVP "John" wrote in message ... I am using the following formula in Excel 2003 =ROUND(AVERAGE(A4:T4),1) and I need to ignore any values that contain a * can any please help?? Thanks in advance John |
Average Function Problem
Jacob,
That fantastic it works really well, many thanks for the response. I have another small issue that you may be able to help me with? 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 * Once again thanks "Jacob Skaria" wrote: Sorry forgot to mention that this is an array fomula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in the Formula Bar you can notice the curly braces at both ends "{=<formula}" -- If this post helps click Yes --------------- Jacob Skaria "John" wrote: Jacob, Unfortunately I copied and pasted your response and the error message appeared #VALUE! Thanks, John "Jacob Skaria" wrote: Try this =Round(AVERAGE(IF(A4:T40,A4:T4)),1) If this post helps click Yes --------------- Jacob Skaria "John" wrote: I am using the following formula in Excel 2003 =ROUND(AVERAGE(A4:T4),1) and I need to ignore any values that contain a * can any please help?? Thanks in advance John |
All times are GMT +1. The time now is 10:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com