Determine item count from the Average function?

[aquixano]

Is there function in Excel which would put the count of numbers entered into an Average function in a cell? For instance, if I have an Average function in cell A1 that has 10 numbers; it would put 10 in say cell B1.

I haven't been able to find any info on this searching, so I thought I poll the experts in the forum. Is this possible?

[MrShorty]

The only such function I am aware of is the COUNT() function. https://support.office.com/en-us/art...4-d23bf411294c

[aquixano]

That would work if I had the 10 numbers in a cells and using the average for the cell range. I don't have a reason to enter the numbers into a worksheet, it's just as easy to put the number directly into the Average function. I just note the last reference in the Average function and enter it in the quantity column. I just was curious if I could use Excel to cut out that step. Thanks for the reply.

[FDibbins]

Maybe Im missing something here, but if you are working out the avg of a range, you are using the =average() function, correct?

If so, that would alreadyt have a range specified - why cant you just use that range for the count() function?

[MrShorty]

It's just as easy to enter the numbers directly into the AVERAGE() function -- IF the only thing you would ever need to do with those numbers is compute the average. However, if I may say so, you have just discovered a reason to enter the numbers into a range and reference that range. You want both the AVERAGE() and the COUNT() of the numbers. At this point, I expect it will be easier to enter the numbers into a range and then invoke both the AVERAGE() and the COUNT() functions on that range.

If you still prefer, you can enter =AVERAGE(list of numbers) and then enter =COUNT(same list of numbers), and not enter the values into their own range, but that seems like it will be less convenient.

I guess what it boils down to -- there is no way to get the AVERAGE() function to also output the count -- which is the real answer to the question in the OP.

[FDibbins]

Another option, which is actually a bit of a step backwards, would be to SUM the range, COUNT the range, then use those 2 values to work out the AVERAGE

[aquixano]

The numbers are entered directly in to the formula like this =AVERAGE(3012.25,3009,2990,2989.50), not into cells on the worksheet - there is no need for them other than in the function. I just make a mental note whether the last one entered was "Number 4" or "Number 24" and enter that in the quantity cell manually. It just struck me that I wondered if there was an attribute for Average in Excel that would output the number count into a cell since the function knows it.

I couldn't find anything about it and and from your replies I think it's safe to call off my search. Thanks all for your replies.

[FDibbins]

aahh well thats a little piece of info that you neglected to mention (or that we did not pick up on)

This should work for you...
=LEN(FORMULATEXT(a1))-LEN(SUBSTITUTE(FORMULATEXT(a1),",",""))+1

[Aardigspook]

@Ford
FormulaText() isn't available in 2010, which is what's in the OP's profile.

@aquixano
Maybe I'm just being dim, but I don't understand why it isn't easier to put the numbers into a range then average that range (or average individual cells within the range). If the numbers aren't going to change, then there's no point in using Average() - you might as well just enter the answer (3000.1875). If the numbers do change, then entering them into cells which the Average function references would allow you also to use Count, as suggested.
(Alternatively, if Ford's suggestion works for you, please update your profile to show which newer version of Excel you're using.)

[aquixano]

Most of the entries are single – quantity 1, and a single price. The “exceptions” will vary from two to 25 entries, so entering into a range would require making the range definition dynamic, which I could do, but the workbook already calc’s a little slow for my taste, so I don’t think adding the additional calculations are worth it. I’ll just keep doing it the way I currently do. My original question was really about if there happened to be some feature unknown to me that adding an argument to the function would give me the count.

I am still on Excel 2010 so the profile is correct. Although I did recently post a question about upgrading since I recently switched to Windows 10. My take away from the comments I received was stick with 32bit unless I had reason requiring 64bit and if 2010 still served me, don’t upgrade. So I decided to leave well enough alone.

Thanks everyone for all the comments.

[AliGW]

You can run the latest version of Office in 32-bit - I am running Office 365 in 32-bit on a 64-bit Windows 10 machine, so that piece of advice is nonsense. Running Office in 64-bit can cause more backward compatibility issues, it's true, especially with VBA, but it's not mandatory.

[aquixano]

I do use VBA a lot, and backward compatibility was another point mentioned that cooled me to the upgrade idea. Thanks.

[AliGW]

Can you provide a sample workbook for this issue?

[aquixano]

If your referring to my original question about Average, unfortunately the workbooks contains private information that I cannot share. However, if that is what your asking about, I could give an example to illustrate. If your not referring to that issue, can you please clarify? Thanks.

[AliGW]

I am referring to the issue you are asking for help with, as per your thread title.

When we ask for a sample workbook, we do not expect you to share sensitive data. We expect a desensitised (dummy) dataset (small) that reflects the real data enough for us to be able to help solve the issue.

[AliGW]

OK, well in the absence of a response, I hope that I have understood correctly.

The attached uses a UDF sourced here: https://stackoverflow.com/questions/...-a-text-string

It's then a case of using the UDF (GetFormula) in this formula to effect the count:

=LEN(SUBSTITUTE(MID(GetFormula(A2),10,99),")",""))-LEN(SUBSTITUTE(SUBSTITUTE(MID(GetFormula(A2),10,99),")",""),",",""))+1

[aquixano]

Understand. Just to be clear / not waste peoples time I wasn't asking about a solution to an issue as much as asking if there was an unknown feature relative to the Average function that would put the number count in a different cell besides the Average. I've created a mock-up workbook with the Average price in D2 and the Total Qty in C2, which I note from the Average function once I'm done entering the numbers in the formula (see picture beneath the function). Since Excel has to "know" the count is 22 to calculate the average, I was just wondering if that piece of info was available somehow. It's not a problem to keep doing what I do, I just wondered if I could do it better using some hidden (at least to me) Excel feature. It's now apparent such a feature doesn't exist. Thanks for you time and interest to inquire.

[AliGW]

I've provided a suggestion above. There is no native feature that does what you want.

[aquixano]

Very interesting - Thank you! I've incorporating your formula into a worksheet change macro as follows:

Please Login or Register  to view this content.

This will work if I can make your formula relative in the macro - that's something that's given me problems in the past. How do I adjust the code to make the code create a relative address vs.an absolute one?

Thanks again for your help!

[AliGW]

That I don't know, but if you like, I can move this to the VBA section where you can get the help you need.

[aquixano]

That's ok - I've found it before, although not sure I ever did it with an array formula. If I can't find the answer after doing some legwork, I'll post a new question in the VBA section (assuming that is acceptable). Let me know if it would be more appropriate under forum rules to just have it moved now. Thanks again for the formula.

[AliGW]

No, on reflection a new thread in the VBA section with an appropriate title would be better.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[aquixano]

Solved marked - Thanks again!