I need to calculate a simple average from some cells. However, some of these cells (not all) contain some text. Specifically, all cells contain a decimal number in between 0.0 and 10.0, while some may also contain an asterisk, e.g. 3.5*.
How do I get to ignore the asterisk and thus include those cells in the calculation of the average? I tried with LEFT, but this would work only if all cells contained the asterisk.
you can use:
=AVERAGE(INDEX(LEFT($A3:$E3,FIND("*",$A3:$E3&"*")-1)+0,0))
assuming your data are in a3 to E3. You may need ; since you're in Belgium.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Thank you very much! Actually I have one addition to this: some cells contain only text, "n.a.". How do I get to skip these without getting back a #VALUE! error?
Welcome to the forum
Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Please find attached a sample worksheet showing my problem
Can you have negative values? If not while this is not elegant, it works.
=AVERAGE(IF(LEFT(SUBSTITUTE($A3:$E3,"n.a.",-1000),FIND("*",$A3:$E3&"*")-1)+0>=0,LEFT(SUBSTITUTE($A3:$E3,"n.a.",-10),FIND("*",$A3:$E3&"*")-1)+0))
You may need ; and... you seem to be using an older version of Excel than me. So, please refer to the attached file. If the formulae are enclosed within a pair of { }, these are array formulae.
These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.
You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks