# 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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Phuoc

##### Active Member
Try this:

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

• Carlos R

#### Phuoc

##### Active 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
3
Views
53
Replies
13
Views
231
Replies
3
Views
221
Replies
9
Views
135
Replies
1
Views
106

### Forum statistics

1,141,060
Messages
5,704,041
Members
421,324
Latest member
Devo182 ### 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