Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.