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
iainmacleodAsked:
Who is Participating?
 
petr_hlucinConnect With a Mentor Commented:
Sorry, now I read your post more carefully. What you probably need is the following:
=MIN(MAX(F11*0.04, 5), 120)

Open in new window

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
 
petr_hlucinCommented:
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))

Open in new window


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
 
wshark83Connect With a Mentor Commented:
how about this:

=IF((F11*0.04)>120,120,IF(SUM(F11*0.04)<5,5,SUM(F11*0.04)))
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
iainmacleodAuthor Commented:
Thanks for the quick response, both work perfectly..
0
 
petr_hlucinCommented:
Since both work perfectly could you please assign several points to me as well?
0
 
iainmacleodAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.