Formula to equal a maximum of 100%
 

I am trying to put together some utilisation figures with a formula where the
answer can not exceed 100%.

I.E if a vehicle can carry a max of 24 pallets per load then it's maximum
utilisation is 100% per load but if the same vehicle does 2 trailer loads in
a day with a combined total of 48 pallets then it's utilisation is still only
100% as it has taken 2 full traileirs or if the second load is only 12
pallets then the overall utilisation is only 75% (36 pallets delivered out of
a maximum 48 possible)

Can anyone please help, every way I try the answer is always over 100%

Formula to equal a maximum of 100%
 

Try the below

A1 = Number of pallets
B1 = Trailer loads

In cell C1 enter the formula
=MIN(1,A1/(B1*24))

and format the formula cell to %

--
Jacob


"Hammer" wrote:

I am trying to put together some utilisation figures with a formula where the
answer can not exceed 100%.

I.E if a vehicle can carry a max of 24 pallets per load then it's maximum
utilisation is 100% per load but if the same vehicle does 2 trailer loads in
a day with a combined total of 48 pallets then it's utilisation is still only
100% as it has taken 2 full traileirs or if the second load is only 12
pallets then the overall utilisation is only 75% (36 pallets delivered out of
a maximum 48 possible)

Can anyone please help, every way I try the answer is always over 100%

Formula to equal a maximum of 100%
 

Hi

With loads in B2:B10 use this formula:

=SUM(B2:B10)/(COUNT(B2:B10)*24)

or if you only have number of pallets delivered in A2 and number of trailers
in B2, use this:

=A2/(B2*24)

Regards,
Per

"Hammer" skrev i meddelelsen
...
I am trying to put together some utilisation figures with a formula where
the
answer can not exceed 100%.

I.E if a vehicle can carry a max of 24 pallets per load then it's maximum
utilisation is 100% per load but if the same vehicle does 2 trailer loads
in
a day with a combined total of 48 pallets then it's utilisation is still
only
100% as it has taken 2 full traileirs or if the second load is only 12
pallets then the overall utilisation is only 75% (36 pallets delivered out
of
a maximum 48 possible)

Can anyone please help, every way I try the answer is always over 100%

Formula to equal a maximum of 100%
 

Not sure whether this is what you have asked for.

A1 Cell
MAX CAPACITY

A2 Cell
48

B1 Cell
CURRENT UTILISATION

B2 Cell
36

C1 Cell
% OF UTILISATION

C2 Cell copy the below formula and paste it.
=IF(A2="","",B2/A2)

Place the cursor in C2 cellDo Right ClickFormat
CellsNumberCategoryPercentageOk.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Hammer" wrote:

I am trying to put together some utilisation figures with a formula where the
answer can not exceed 100%.

I.E if a vehicle can carry a max of 24 pallets per load then it's maximum
utilisation is 100% per load but if the same vehicle does 2 trailer loads in
a day with a combined total of 48 pallets then it's utilisation is still only
100% as it has taken 2 full traileirs or if the second load is only 12
pallets then the overall utilisation is only 75% (36 pallets delivered out of
a maximum 48 possible)

Can anyone please help, every way I try the answer is always over 100%