Formula to Average column D:D that contains a combination of numbers, text and blank cells
I need to write a formula that will average the numbers in column D. The formula needs to ignore anything in D:D that is text or any cell that is blank. Note, the formula needs to deal with the entire column D (thus, the D:D) because the amount of data varies widely.
not sure what you mean
=AVERAGE(D:D)
should work fine, if you look in the help of Excel it shows that
if your data contains errors, it will fail, but text and blanks get ignoredIf a range or cell reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
Apologies for the example. A simple =AVERAGE would handle that. BUT the actual spreadsheet I'm working for refers to other locations in the data. It averages when it is just the numbers being referenced, but does not when the text is in the mix. Give me a moment to go and edit the file and edit it for better review.
Tested this: I think it will give you what you want
=AVERAGE(D5:D241)
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
well if you insist, but the AVERAGE(D:D) still works, it is based on the value of the cell, not how it is derived,text and blanks get ignored, just as your OP says you want....if you only want cells based on specific criteria, then maybe averageif or averageifs,
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks