Help me with formula

[vj1030]

Hello Sirs

Pls help me with formula for below criteria


1) if Cell B qty >0 then select max between two differnt cell for B&X, upon this result get min qty of cell X&Z

[pre]

part    cell qty B  cell X   Cell Z
9121-u   20          200      120

[/pre]
Thanks

Vijay

 

[Luke M]

Not completely sure what you're asking for with the "upon this result get min qty" bit, but perhaps this is what you wanted:

=IF(B2>0,MAX(B2,X2),MIN(X2,Z2))


If not, could you add to your example and show some cases of inputs and desired outputs?

 

[vj1030]

Hi Luke..

sorry i think i have confused you..

Formula till =IF(B2>0,MAX(B2,X2) is correct

what i need further is Min of above formula result and Z2


Pls help me


Vj

 

[Hui]

=Min(IF(B2>0, MAX(B2, X2)), Z2)

You need to be careful as if B2<=0 it will return an error

So maybe it should be

=Min(IF(B2>0, MAX(B2, X2),"B2<0"), Z2)

or another message, formula or value.

 

[oldchippy]

Hi,


Or maybe


=IF(B2>0,MAX(B2,X2),IF(B2<=0,MIN(X2,Y2),""))

 

[Luke M]

Concerning Hui's formula, I don't think you want to have the text result within the MIN statement, as it will just get ignored by the function and you'll only get the value of Z2. Rearranging things a little:

=IF(B2>0,MIN(Z2,MAX(B2,X2)),"B2 is less than 0")

 

[Learner]

Hello Sir,


I have a question on making a formula. I have built my own simple personal money management worksheet. I want to know how I can write a formula which can automatically deduct mortgage amount from my income every month by the 15th for the next 5 years. I have this data for the mortgage (monthly EMI) as an amortization table in the sheet and now I am manually doing it every month which gets automatically added to the expenses and simultaneously gets adjusted from my incomes. I tried lots of formulas mainly with IF but I cannot do it for more than a month. So do you think of any ways by which when I open my worksheet on a 15th the corresponding data for the 15th from the amortization table is automatically filled in the mortgage column under expenses and get deducted automatically from my income in my work sheet ?


Gross Income A1

Net Income A10

Expenses D1

Net Expenses D10

System Date : B1 (Now () May 15, 2012)


eg. Amortaziation Table EMI : A20: A 79 (60 months)

Amortaziation Table dates : B20: A 79 (15/mm/yy)


Thanks for your efforts on my behalf.


Rams

 

[Montrey]

@Learner


Please make a new Thread. What your doing is called "hi-jacking" someone else's thread.

Also please post an example workbook when you do. This sounds like an easy problem to fix.


:D

 

[Learner]

Hi Montrey,


OPPS ! Sorry.


That was not my intention at all.


My Apologies to anyone affected by my unintended actions.


Will do as you recommended.


RM