How to exclude the text value in Average Function??

[nigel_12]

I try to use the Average function in my spreadsheet.
And I find that it includes the text in the first row automatically. How can I exclude it?
Average.xlsm

In the Sheet"ProcessTable", Range "J11" The average will be Average(E1:E11) , but you can notice that E1 is "Close" instead of an numerical value.

How can I solve this?

Thanks.

[dilipandey]

but you can notice that E1 is "Close" instead of an numerical value.

E1 will be "Close" only because it is an value hard coded (entered manually).. I am not able to understand what you are looking for ? thanks.


Regards,
DILIPandey
<click on below * if this helps>

[Ron Coderre]

Using your posted workbook...
Try something like this:
J2:

Please Login or Register  to view this content.

Copy that formula down as far as you need.

Is that something you can work with?

[nigel_12]

Yes, this is what i need!
And could you explain how the Row() function works?

[Ron Coderre]

In this formula: =IFERROR(IF(AND($E2>0,ROW()>UserInput!$E$10),AVERAGE($E2:OFFSET($E2,,,-UserInput!$E$10,)),""),)

This section AND($E2>0,ROW()>UserInput!$E$10) determines if
1) the E2 value is greater than zero
AND
2) the current row number is greater than the number of cells we want to average.
Effectively making sure we only calculate if we have the minimum number of values we want to average.

If your headings were on Row_4, the formula could be adjusted to this:
=IFERROR(IF(AND($E4>0,(ROW()-ROW($J$3))>=UserInput!$E$10),AVERAGE($E4:OFFSET($E4,,,-UserInput!$E$10,)),""),)

[nigel_12]

Clear and accurate answer! ThankS a lot sir

[nigel_12]

One more question.

When I calculating 3 SMAs, the second and the third one will be affected by the first one.
I used the formula you amended, Why would it happen?

thanks

[Ron Coderre]

Each column's formula needs to reference the input cell that affects it:

Please Login or Register  to view this content.

Does that help?

[nigel_12]

Yes. I just omitted to change the first E10. Thank you for you help

[nigel_12]

One more question.

I have made a sub, which calculate the mean deviation (Column Q)
The mean deviation is "SMA of Typical Price(Column P )" minus "Typical Price(Column H). the Rane is according to the CCIday which is located in Sheet(UserInput) B10
For example, =abs(P4-H4)+abs(P4-H3)+abs(P4-H2) if the4 CCIday is "3"

Could you please tell How can i code it for calculation?

Again, many thanks

[nigel_12]

Anyone could help me to solve this?

[nigel_12]

Please help me to solve this.