**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.*