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

Carlos R

New Member
Joined
Jul 10, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!!
 

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
Joined
Apr 29, 2016
Messages
415
Office Version
  1. 2016
If you want to use the old formula, try this:

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

Carlos R

New Member
Joined
Jul 10, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
13,528
Office Version
  1. 365
Platform
  1. Windows
You could also use
Excel Formula:
=MEDIAN(0,1,x*y)
 

Forum statistics

Threads
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.
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
Top