Conditional Minimum??

[Gitanes081280]

Hello everybody.
I'm new here, and this is my very first post.



I want to create a formula for ($Rate/Gram) x (Gram), but requiring a few conditions.

It requires the minimum of $10.
When the Rate is $0.00/Gram, the target cell has to show $0.00

I was able to come up with a formula such as below:

“F30(cell where the formula is)” =MAX(10,(ROUND(SUM(E25*E30),2)))

One problem is that when E30 turns out to be $0.00/Gram, F30 still shows $10.00, for obvious reason.
Because the Minimum is set to be $10.00, F30 amount doesn't reflect back to E25*E30.

Is there any “IF formula” that can be added here so when E30 is $0.00, F30 shows $$0.00, but when E30 is $0.01 or more and F30 is under $10.00, F30 retains the Minimum of $10.00, and at the same time, when F30 is over $10.00, it uses E25*E30 amount rounded up to two decimals?

I was thinking about creating a “Minimum Column” on G30 to isolate the $10.00 Minimum (if it does any good, that is).

I've been trying to some up with the solution for months, and was about to give up, but decided to give this forum a try since a lot of experts seem to be here.

Thank you in advance for your help.


Best Regards,



Gitanes081280

[mehmetcik]

Please Login or Register  to view this content.

[/formula]

[Gitanes081280]

Dear Mehmetcik,



Thank you for your reply.

I tried the formula; however, F30 value does not change until the E30 goes above $10.00.
(E25 at 700grams) x (E30 at $0.10/gram) still comes out at "$10.00 Flat Rate" when it's supposed to be $70.00.

I experimented with changing number in your formula as below:

=if(e30=0,0,if(e30<0.01,10,roundup(e25*e30,2)))

However, in this formula, the F30 Cell no longer recognizes the Minimum $10.00 anymore.
(E25 at 700grams) x (E30 at $0.01/gram) comes out as $7.00

Would you be able to come up with a solution for this, please?
I hate throw everything at someone without putting in my own effort, but this formula itself is way above my skill.

Thank you in advance for your help.

** If anyone else has any ideas and inputs, please share. Anything helps. **


Best Regards,



Gitanes081280

[bebo021999]

How about:

=IF(and(E30>=0.01,roundup(E25*E30,2)<10),10,roundup(E25*E30,2))

[Gitanes081280]

Dear bebo021999,



Thank you so much for your help!

As far as I've been checking the numbers, this formula is working perfectly.

For the past several months, I spent hours and hours a day, trying to come up with a formula.
I made one post here, and the problem was solved overnight.

I wish, one of those days, I become familiar enough with Excel to help others, like you helped me here.

Thank you again, both of you, for your kindness.


Best Regards,



Gitanes081280

[bebo021999]

You are welcome.
please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.