How to properly write A1*B1 with minimum of Zero and Maximum of A1

[Carlos R]

Out of sheer desperation, I am writing this to my Universe of Friends whom I think have Excel Skills.


I know that the answer is right in from of me, but I just can’t see it. I have been working on this for HOURS!!

Not YOUR problem, but if you can easily see something that I can not, I would be eternally grateful if you could point me in the right direction.

I am trying to write a formula in which both of the following conditions are met in the same cell.


1) If X times Y is greater than or equal to X=10, then display "10".
=if((X*Y)>=X,X)

If X=10 times Y is equal to or less than "0", then display "0".
=if((X*Y)<=0,0)


Individually, each condition works. However I can NOT figure out how to string them together in the same cell so that it will only display results from 0 to 10


I am trying to say =if((X*Y)>=X,X) and/or =if((X*Y)<=0,0)


I need to know how to properly write the conjunction for the two conditions.

=if((X*Y)>=X,X) ????? =if((X*Y)<=0,0)

As I was writing this I got some help on FB and the following formula satisfies both conditions, BUT read FALSE for anything between 0 and X.

=if((X*Y)>=X,X),if((X*Y)<=0,0)

X*(Y=1) will read TRUE
X*(Y=1.5) will display X as desired
x*(Y=-1.5) will display 0 as desired
However X*Y, where the product is from 0 to X will read FALSE.

I think I need a third Condition which asks for the products between 0 and X.

I am not really asking it to give me only the number between 0 and X.
The two conditions are asking to display X if the result is greater than X AND if the result is Less than Zero, display "0"
I need a third condition which asks for the numbers between 0 and X.
Maybe I need to ask for it conversely, Give me X*Y from 0 to X.

Any guidance would be greatly appreciated!!

 

[Phuoc]

Try this:

=MAX(0,MIN(X1*Y1,X1))

 

[Phuoc]

If you want to use the old formula, try this:

=if(X*Y>=X,X,if(X*Y<=0,0,X*Y))

 

[Carlos R]

Try this:

=MAX(0,MIN(X1*Y1,X1))

My initial test seems to have worked. Thank you so much. I can get some sleep without fretting. I will keep you posted.

 

[Carlos R]

If you want to use the old formula, try this:

=if(X*Y>=X,X,if(X*Y<=0,0,X*Y))

I took an excel class in the early 90s, if not earlier and this seems more familiar.
My skills are very basic. I read it as;
If X times Y is greater than or equal X then display X.
If X time Y is Less than or equal to Zero, display "0" otherwise/else X times Y.

Greatly Appreciated!
Carlos

 

[jasonb75]

You could also use

=MEDIAN(0,1,x*y)