Question description:
This user has given permission to use the problem statement for this
blog.
How do I find the numbers whose average is equal to 321. Each of the 6 numbers has a parameter. (0,50),(51,100),(101,150),(151,200),(201,300),(301,500).
Solved by M. A. in 37 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
05/10/2018 - 06:43
Hi…Welcome to Got It Pro. I see that your question is about fixing formulas.
User
05/10/2018 - 06:43
yes
Excelchat Expert
05/10/2018 - 06:43
I can help you with that problem.
Excelchat Expert
05/10/2018 - 06:43
Before we get started, this is a quick reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
User
05/10/2018 - 06:43
ok
Excelchat Expert
05/10/2018 - 06:44
Could you please elaborate a little on what exactly you require?
Excelchat Expert
05/10/2018 - 06:44
It will be of great help if you could illustrate in the preview sheet.
User
05/10/2018 - 06:46
the values in B9:G9 need to be determined by the parameters in B1:G1, and must be equal to the AQI value in B7
Excelchat Expert
05/10/2018 - 06:47
Do you mean to determine the Level of Health concern in B7?
Excelchat Expert
05/10/2018 - 06:48
I can create a formula for that.
User
05/10/2018 - 06:48
no i am able to do that easily once i find the numbers in B9:G9
User
05/10/2018 - 06:49
their average, within those parameters for each column, needs to equal 321
Excelchat Expert
05/10/2018 - 06:49
Oh ok..so you mean AQI value in cell B6?
User
05/10/2018 - 06:49
yes
Excelchat Expert
05/10/2018 - 06:51
Could you please give an example in this scenerio?
Excelchat Expert
05/10/2018 - 06:51
What numbers should appear in row 9?
User
05/10/2018 - 06:52
See B1, B9 should be a number between 0-50
Excelchat Expert
05/10/2018 - 06:52
Let me check my understanding.
User
05/10/2018 - 06:52
and so on, row 1 are the parameters
Excelchat Expert
05/10/2018 - 06:53
B9 should be any number between 0-50
User
05/10/2018 - 06:53
yes
Excelchat Expert
05/10/2018 - 06:53
C9 should be any number between 51 - 100
Excelchat Expert
05/10/2018 - 06:53
and so on
Excelchat Expert
05/10/2018 - 06:53
And then the average must equal to 321 for all 6 categories.
User
05/10/2018 - 06:53
correct
Excelchat Expert
05/10/2018 - 06:53
Is my understanding correct?
Excelchat Expert
05/10/2018 - 06:53
Ok..got you. Let me build the formula for B9 to G9.
Excelchat Expert
05/10/2018 - 06:54
This will take me couple of minutes. Please be with me.
Excelchat Expert
05/10/2018 - 06:54
Please do extend the time when prompted. Thanks!
User
05/10/2018 - 06:54
OK
Excelchat Expert
05/10/2018 - 07:07
Does the values in B9:C9 need to be always integers?
User
05/10/2018 - 07:07
yes
Excelchat Expert
05/10/2018 - 07:09
I am doing this through solver and I am adding all the constraints.
Excelchat Expert
05/10/2018 - 07:18
[Uploaded an Excel file]
Excelchat Expert
05/10/2018 - 07:18
I have added the solver scenerio in this file. Please go to Data >> Solver and click Solve.
Excelchat Expert
05/10/2018 - 07:19
Unfortunately, it is unable to solve for average 321 with the applied constraints.
Excelchat Expert
05/10/2018 - 07:19
But you can check for other values.
User
05/10/2018 - 07:19
Thank you for your help
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.