Question description:
This user has given permission to use the problem statement for this
blog.
formula to tell if column has "X" amount of containers and 87% is required to fill container - based on "y" total plan volume and "z" actual volume, how can i calculate if 87% was available for 1, 2, 3, ect containers (X) based upon above criteria and show how many containers could have been filled to at least 87%
Solved by F. A. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
13/04/2018 - 11:30
often
Excelchat Expert
13/04/2018 - 11:30
Hello :)
User
13/04/2018 - 11:30
hello
Excelchat Expert
13/04/2018 - 11:31
Are you able to share your data?
User
13/04/2018 - 11:31
i made a file too big. can i add in the side and you can see?
Excelchat Expert
13/04/2018 - 11:31
Yes, that'd be perfect. Thank you :)
Excelchat Expert
13/04/2018 - 11:34
I am just going to wait until you have all the data if that is okay.
User
13/04/2018 - 11:36
ok, so...if 1.79 is the amount of space i had available in column d. how can i find out, of the 2 containers, if 87% or more of each container (column a) could be used - based on Column b planned volume and column c actual volume. so like was 1 container avail and 1 was not avail
Excelchat Expert
13/04/2018 - 11:37
I'm reviewing your question now.
Excelchat Expert
13/04/2018 - 11:38
Okay, so the total column is the amount of containers?
User
13/04/2018 - 11:38
yes
Excelchat Expert
13/04/2018 - 11:38
planned is the amount of space you planned for.
Excelchat Expert
13/04/2018 - 11:38
cubed size is the actual space available.
Excelchat Expert
13/04/2018 - 11:38
What does D represent?
User
13/04/2018 - 11:39
cube space - based on plan divided by size
User
13/04/2018 - 11:39
so like 153....if that is plan. and 133 is size, that's 87%
Excelchat Expert
13/04/2018 - 11:40
So 1.79 is 179%?
User
13/04/2018 - 11:41
no.no. that 1.79 containers that had enough volume
User
13/04/2018 - 11:41
i need to know...of those 2
User
13/04/2018 - 11:41
how many had 87% or more. so 1 would have 87% and the other would not
Excelchat Expert
13/04/2018 - 11:41
I see, so it takes 1.79 containers to hold the volume.
Excelchat Expert
13/04/2018 - 11:42
I understand now. Thank you.
Excelchat Expert
13/04/2018 - 11:42
Can the 1 have 100%?
Excelchat Expert
13/04/2018 - 11:42
and the other have what is leftover?
User
13/04/2018 - 11:42
yes
Excelchat Expert
13/04/2018 - 11:43
And the answer you are seeking is how many containers will be greater than 87% full?
User
13/04/2018 - 11:43
yes...and how many wont
Excelchat Expert
13/04/2018 - 11:43
Okay, and the column D will be given?
User
13/04/2018 - 11:43
so if the containers change to 3. then the answer should be 1 container was available for use....and 2 were not
User
13/04/2018 - 11:43
yes
User
13/04/2018 - 11:44
the only variable is column a
Excelchat Expert
13/04/2018 - 11:45
So question, couldn't this be dermined by the number in column D.
Excelchat Expert
13/04/2018 - 11:45
?
Excelchat Expert
13/04/2018 - 11:46
for instance column D is 1.79
User
13/04/2018 - 11:46
that will change based on if the plan and cube size changes
Excelchat Expert
13/04/2018 - 11:46
that means that one container will be 100% full and the second container will be 79% full.
Excelchat Expert
13/04/2018 - 11:46
But that is not the formula you are looking for correct?
User
13/04/2018 - 11:46
correct. but the containers must be at least 87% full to use
User
13/04/2018 - 11:47
correct
Excelchat Expert
13/04/2018 - 11:47
Okay, perfect.
Excelchat Expert
13/04/2018 - 11:47
I have a solution for you.
User
13/04/2018 - 11:47
i need to know, how many containers will use 87% of column c
User
13/04/2018 - 11:47
*at least 87%
User
13/04/2018 - 11:48
ok great
User
13/04/2018 - 11:48
sorry - 87% of column b. not c
Excelchat Expert
13/04/2018 - 11:50
Okay, I see.
User
13/04/2018 - 11:50
ill make a quick change to show
User
13/04/2018 - 11:51
so now the answer should be 1 container has enough to fill 1 container. the other does not have enough
Excelchat Expert
13/04/2018 - 11:54
I believe the solution is in C5 and D5 unless I'm not understanding your logic yet.
Excelchat Expert
13/04/2018 - 11:54
It seems that column D will drive your answer.
Excelchat Expert
13/04/2018 - 11:54
if column D represents the number of containers that will be needed.
User
13/04/2018 - 11:55
yes
Excelchat Expert
13/04/2018 - 11:56
so C5 will round to the nearest int.
User
13/04/2018 - 11:56
it will be 1, 2, 3, etc
Excelchat Expert
13/04/2018 - 11:56
then, the decimal piece can be solved by using an if statement.
Excelchat Expert
13/04/2018 - 11:56
Same with the containers <87%
User
13/04/2018 - 11:56
yes
Excelchat Expert
13/04/2018 - 11:57
if that decimal is less than 87%, then it will show 1 container not used.
User
13/04/2018 - 11:57
yes
Excelchat Expert
13/04/2018 - 11:58
So is this solving your request?
User
13/04/2018 - 11:59
no, because if its 3 containers now, less than 87% SHOULD CHANGE
Excelchat Expert
13/04/2018 - 11:59
Ah, one moment. I will add that in.
User
13/04/2018 - 12:00
that's it i think. 1 sec
Excelchat Expert
13/04/2018 - 12:00
Okay, thank you for your patience in explaining this to me.
Excelchat Expert
13/04/2018 - 12:00
It was very complicated to grasp for some reason.
User
13/04/2018 - 12:01
its really weird to explain. thanks for your patience
Excelchat Expert
13/04/2018 - 12:01
It's been my pleasure to work on this for you. Very challenging and rewarding :)
User
13/04/2018 - 12:02
thank you and have a great day
Excelchat Expert
13/04/2018 - 12:02
Thank you. You also.
Excelchat Expert
13/04/2018 - 12:02
Please be sure to end the session and rate your service.
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.
