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

#### Carlos R

##### New Member
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.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!!

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### Phuoc

##### Well-known Member
Try this:

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

• Carlos R

#### Phuoc

##### Well-known Member
If you want to use the old formula, try this:

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

• Carlos R

#### Carlos R

##### New Member
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

##### New Member
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

##### Well-known Member
You could also use
Excel Formula:
``=MEDIAN(0,1,x*y)``

Replies
0
Views
91
Replies
2
Views
56
Replies
5
Views
179
Replies
0
Views
153
Replies
3
Views
321

### Forum statistics

1,191,032
Messages
5,984,241
Members
439,879
Latest member
KingGoulash ### 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.

### Which adblocker are you using?    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

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