• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

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
0
iainmacleod
Asked:
iainmacleod
  • 3
  • 2
2 Solutions
 
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
 
petr_hlucinCommented:
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
 
wshark83Commented:
how about this:

=IF((F11*0.04)>120,120,IF(SUM(F11*0.04)<5,5,SUM(F11*0.04)))
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Featured Post

Get expert help—faster!

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

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now