Help Need...

[Ashfaque]

Hi,

In 4 cells in Excel sheet, I have to calculate remainder.

User has to send number of pallets in one truck load. Each pallet has fix number of bags load.

Cell A1 holds number of bags per pallet
Cell B1 holds pallet
Cell C1 holds number of bags to be sent to client
Cell D1 where I can manage pallet required.
But only need help at Cell F1 where the remainder is required (The excess bags qty after availble pallet(s) are over)

This is something like below

Bags/ Pallet Pallet Bags to be sent Pallet required Reminder
256 1 300 1 44
128 1 425 3 41
64 1 55 1 -9

How can get it done in excel.

I tried with =If(cond… the..do this…else do this) but not worked out.

I can do it in MS Access but client need it in Excel only.

Can somebody help me here please...

Regards,
Ashfaque

 

[qafself]

Try MOD(C1,A1)

 

[Brianwarnock]

Building ob Ed's answer, if I understand correctly I think that you want
=IF(D1*A1<C1,MOD(C1,D1*A1),"")

Brian

 

[Ashfaque]

Thanks Guys,

I think I should have to explain more clearly.

If the capacity of pallet is for example 128 bags and we have 425 bags to load over so it means we need 3 pallet so we can adjust 384 bags and the rest qty from 425 will be then 41 bags.
I need to show this 41 in F1 cell.

Another example...say if the capacity of pallet is 128 but we have let us say 95 bags. In this situation off course the required qty of pallet will 1 even it will have empty space to accommodate more bags. But the total bags are 95 so it means if F1 cell it should show -33

I hope I explained clearly now...

Expecting your response....

Regards,
Ashfaque

 

[qafself]

See attached - you will need to adjust cells to reflect your sheet

 

[musthu]

is this ok

 

[Brianwarnock]

So you simply want the (Bags to be sent)-(bags per pallet*Number of pallets)



That looks far too simple.

Brian

 

[MSAccessRookie]

Thanks Guys,

I think I should have to explain more clearly.

If the capacity of pallet is for example 128 bags and we have 425 bags to load over so it means we need 3 pallet so we can adjust 384 bags and the rest qty from 425 will be then 41 bags.
I need to show this 41 in F1 cell.

Another example...say if the capacity of pallet is 128 but we have let us say 95 bags. In this situation off course the required qty of pallet will 1 even it will have empty space to accommodate more bags. But the total bags are 95 so it means if F1 cell it should show -33

I hope I explained clearly now...

Expecting your response....

Regards,
Ashfaque

What about the MOD command?

[/FONT]
[FONT=Courier New]IF[/FONT]
[LIST]
[*][FONT=Courier New][COLOR=#ff0000][COLOR=black]A1 =[/COLOR] 256[/COLOR] [/FONT]
[*][FONT=Courier New]B1 = [COLOR=blue]1[/COLOR] [/FONT]
[*][FONT=Courier New][COLOR=black]C1 = [/COLOR][COLOR=darkorchid]300[/COLOR] [/FONT]
[/LIST][FONT=Courier New]THEN[/FONT]
[LIST]
[*][FONT=Courier New]D1 = (C1 - MOD(C1, A1))/A1   [COLOR=red][B](1)[/B][/COLOR][/FONT]
[*][FONT=Courier New]F1 = MOD(C1, A1)             [COLOR=red][B](44)[/B][/COLOR][/FONT]

[/LIST]

 

[Bilbo_Baggins_Esq]

Musthu,
In your model, if the amount of bags to send is less than half of the capacity of the pallet, 0 will be returned for pallets required.

I’m not sure why you others are stuck on the MOD() function.
Sure it returns a remainder, but in this usage, ends up being more complicated.

Qafself,
Yours is a good example of this.
You correctly use the INT() function to get the base number of pallets required including an IF() test to see if Bags to send is less than the pallet capacity. This is correct, but then you make no use of that calculation.
Instead, you go back to using the MOD() function which, in this circumstance, requires another test to see if Bags to send is less than the pallet capacity.
Actually, at that point, all you need is a simple calculation, Bags to send-(Pallet Capacity*Calculated Pallets)

Ashfaque,
I have made a sheet using your original example and it is attached.
As is almost always the case with Excel, there is more than one way to everything.
The above methods will work, but in my opinion, using MOD() requires an additional IF() condition when really only one is required in the initial pallet calculation.

 

[Brianwarnock]

Actually, at that point, all you need is a simple calculation, Bags to send-(Pallet Capacity*Calculated Pallets)

QUOTE]

I could have sworn that I said that a couple of posts back.

Brian

 

[Bilbo_Baggins_Esq]

oops, good thing it wasn't a poisonous snake!

sorry

 

[Ashfaque]

Thanks gentlemen,

I was reading and doing practicle all ideas you gave me.

I really appreciate your concern.

qafself

Your example is really nice using INT and MOD that gave the correct result.
I thank you for this trick.

musthu

Your exmple is fine too but 2 things I found in it. 1 if used half of the pallet capacity qty, it displays ZERO VALUE.

Moreover, if user need 50 bags to sent, it shows 50 as REST QTY.

But you tried in very good way to explain the things around.

I thank you for your efforts.

MSAccessRookie
Thanks Rookie for your effort too.
But the model at one point need to be updated. If user is entering less qty to be sent (even 1 less), the Pallet being displayed as 0 and affects remainder as well.

Bilbo_Baggins_Esq
Your modified sample is excellent that produced the result I wanted. This covered those points more or less not covered before.

Brianwarnock
Thanks to Brian for the initial code. It was good enough to start with.
I again thanks to all of you gentlemen.

With kind regards,