Forum Discussion
If calculation returns a negative number, then it needs to be a 0; 2nd part is to show Max number
1. For row 45, I have calculation for 30% of row 42 ex. =F42*.30. If the result is a negative number, then I would like it show a 0 instead of the negative number.
2. Next, for row 48, the result of =F9-F45 can be no greater than F9. So if it is greater than F9, then F9 is the highest number that it should show, in this case $281, but should still show values 0 up to the amount of F9.
Thank you
Do you know there are functions MIN and MAX?
In this case, the MAX function is what you need.
In row 45, for example, =MAX(0,F42*.30) so if the result of F42*.3 is negative, 0 is greater (MAX); if the result is positive, then it displays the result.
Similarly, =MAX(F9,F9-F45)
A suggestion for you (my guess is you're learning Excel at this point): when you think to yourself, "There has to be a function or formula that can do this", realize you're 99.99% likely to be right, there IS a solution. And in this case the very fact that you're using the term Max is a good place to start looking. And then, even if it takes some trial and error, once you've found that function, play with it. You're not going to break anything, and you'll learn. Probably in less time than it took to wait for this answer.
Thank you so much! I have used MIN and MAX but could not figure out how to get it to work and maintain the calculations. This worked so I will refer back to this. I have been trying to get this to work for 6 hours. I could not scour the internet any longer and decided to ask for an expert. Your answer came much faster haha.
