Help need creating a formula.

crazy_papaya

New Member
Joined
Feb 10, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
  2. Web
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 CodeCurrent StockRequiredTransfer FromRemaining StockTransfer Amount
DR0012
10​
30​
DR0033
20​
20
DR0013
5​
24​
DR0034
66​
DR0014
0​
40​
DR0035
3​
DR0015
20​
60​
DR0036
-75​
 
How about
=IF(D2>2,"Transfer not required",IF(MIN(F2,K2)<0,"Insufficient Stock",MIN(F2,K2)))
 
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hello Fluff, are you there somewhere? I am having difficulty yet again to combine two formulas together. Believe me i tried... But I'll ask from zero to make things simpler -

Product (A)Available at Ontario ( B )Required at Burnaby ( C )Final Transfer Qty ( D )
DR001
4​
3​
DR002
-9​
10​
DR003
4​
6​

Conditions -

  • D2 should be the MIN of B2 and C2
  • If B2 is less than C2 than the message should prompt " Insufficient Stock"
  • If C2 is less than or qual to 0 the message should say " Stock Available"
 
Upvote 0
Umm the criteria below is the main formula -
  • D2 should be the MIN of B2 and C2
Then -
  • If C2 is less than or equal to 0 the message should say " Stock Available"
Last -
  • If B2 is less than C2 than the message should prompt " Insufficient Stock"

Product (A)Available at Ontario ( B )Required at Burnaby ( C )Final Transfer Qty ( D )
DR001
4​
3​
DR002
-9​
10​
DR003
4​
6​
 
Upvote 0
umm It should say "Insufficient stock" because B3 is less than C3

So, i guess I told you the criteria in wrong order ? :oops:
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top