# Excel Formula

I have the following formula that works.
=IF((F11*0.04)>120,120,(F11*0.04))

I need to add a section that would calculate a minimum value of say 5. As in this formula
=IF(SUM(F11*0.04)<5,5,SUM(F11*0.04))

I basically need a combination of the two, so F11*0.04 with a minimum value of 5 amnd a maximum of 120.

Any help greatly appreciated
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
1) There is a MIN(...) function in Excel which is more comfortable to use in the case you describe, e.g. the initial working formula would be:
``````=MIN(120,(F11*0.04))
``````

2) What do you mean by "section"? You don't need to use SUM(...) function for a 1-item list like SUM(F11*0.04).
0
Commented:
Sorry, now I read your post more carefully. What you probably need is the following:
``````=MIN(MAX(F11*0.04, 5), 120)
``````
This formula will return F11*0.04 if it is in range of 5..120, otherwise it will return 5 (if it is smaller), or 120 (if it is greater).
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:

=IF((F11*0.04)>120,120,IF(SUM(F11*0.04)<5,5,SUM(F11*0.04)))
0
Author Commented:
Thanks for the quick response, both work perfectly..
0
Commented:
Since both work perfectly could you please assign several points to me as well?
0
Author Commented:
I had to ask for the question to be reopened as I had meant to accept both solutions. This is now done. Thanks to both
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.