I have an excel sheet with average formulas. Within these average formulas there a fair amount of data.
Example
Average(5,10,15,8) which is 9.5
Is there any way in a different cell I can have a formula that would add the numbers inside the average formula? Inside the average formula is the only place I have the numbers. So for the example above I would like this new cell to tell me 38. Is this even possible? In the end, I would like to have both an average and sum cell.
Welcome to the forum.
There are instructions at the top of the page explaining how to attach your sample workbook.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I only have the numbers within the average formula. I would like to have them both and when I add a new number into the average formula have a sum formula add all the numbers in the average formula. The value are not in a table. Only in the average formula. I would like to have this sum formula update as I enter more numbers into the average formula. I'm just curious if this is even possible.
I don’t know, to tell the truth. I thought you meant that they were part of a more complex formula. You might be able to do something with FORMULATEXT.
Do you have Office 365? There is no Excel 2020 yet.
Wouldn't it be easier, as you add numbers to add each number to a new cell, ie. A1=5, A2=10, A3 = 15, etc.
Then your average formula would be =AVERAGE(A1:A100) and Sum would be =SUM(A1:A100)
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
As an exercise, here's what I did to extract and sum the numbers using a formula
With your AVERAGE Formula in cell B1
I created a relative defined name (in case you plan on doing this down a column) called Extraction (in the Defined Name Manager on Formula Tab)
My active cell is C1 (just to the right of the Average Formula.
Extraction =
. Then the formula in C1Formula:
Please Login or Register to view this content.
See attachmentFormula:Please Login or Register to view this content.
=a1*(len(formulatext(a1))-len(substitute(formulatext(a1),",",""))+1)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks