Help need creating a formula.

[crazy_papaya]

Hello all,

I am trying to put it all in one formula together but failing. It might be easier for you i think, please see if you can assist me -

1) H2 should be equal to C2 but if G2 is less than C2 then H2 should G2
2) If H2 is in negative then it should be either "0" or " No transfer required "

Drug Code	Current Stock	Required	Transfer From	Remaining Stock	Transfer Amount
DR0012	10​	30​	DR0033	20​	20
DR0013	5​	24​	DR0034	66​
DR0014	0​	40​	DR0035	3​
DR0015	20​	60​	DR0036	-75​

 

[crazy_papaya]

Oh no! this is wrong. I am trying to delete / edit

 

[Fluff]

Can you please post a sample of your data using the XL2BB add-in. That way we can tell which column is which.

 

[crazy_papaya]

Can you please post a sample of your data using the XL2BB add-in. That way we can tell which column is which.

Thank you Fluff, working on it

 

[Fluff]

 

[crazy_papaya]

Sorry Fluff, I couldn't delete the entire post so I though I'd make a new one :P

Anyway, I'll put the question here -

1) F2 should be equal to C2 but if E2 is less than C2 then F2 should E2
2) If F2 is in negative then it should be either "0" or " No transfer required "

Drug Code (A)	Current Stock (B)	Required (c)	Transfer From (D)	Remaining Stock (E)	Transfer Amount (F)
DR0012	10​	30​	DR0033	20​
DR0013	5​	24​	DR0034	66​
DR0014	0​	40​	DR0035	3​
DR0015	20​	60​	DR0036	-75​

 

[Fluff]

How about
=MAX(0,MIN(C2,E2))

 

[crazy_papaya]

Hahaha You make it look so easy

Yes it is working, Thank you and sorry about the rule breaking!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[crazy_papaya]

Hello Fluff or anyone else who is listening, can you please help me with an imbedded If statement I am trying to create -

(A) Drug Code	(B) Weekly Usage	( C ) Current Stock	(D) Current WoS	( E )New WoS	( F) Transfer	( G) Transfer From	( H) Current Stock	( I )Weekly Use (Min Stock Reqd)	( J )Stock Required	(K) Remaining Stock	( L) Transfer Amount
DR001	25​	30​	1.2​	3​	74​	DR005	125​	30​	60​	65​	65​
DR002	10​	7​	0.7​	3​	29​	DR006	50​	30​	60​	-10​
DR003	10​	23​	2.3​	3​	7​	DR007	100​	20​	60​	40​

L2 already has a working Formula =MIN(F2,K2)
I am trying to create a formula which adds the 2 conditions below along with the formula (=MIN(F2,K2))

Conditions -

1) if L2 is in Negative it should give the message " Insufficient Stock "
2)Also, D2 > 2 than the message should prompt " Transfer not required "