So basically I am creating a spreadsheet for a budget. I need help creating a formula.
if H5:H7 is less than 2000 then K6 = 300
but if H5:H7 is greater than 2000 but less than 4000, k7 needs to = 600.
and if H5:H7 greater than 4000 then k7 needs to equal 900.
how would this formula be input?
Last edited by DJAMESRUSHLOW; 07-22-2018 at 05:14 AM.
Assuming that the first line should say K7 = 300, not K6 = 300, try
=MIN(900,CEILING(SUM(H5:H7),2000)*0.15)
Please read the rules that you agreed to follow when you joined the forum https://www.excelforum.com/forum-rul...rum-rules.html and edit your thread title accordingly if you require any additional help with this. Thanks.
Last edited by jason.b75; 07-22-2018 at 05:57 AM.
try =IF(SUM(H5:H7) < 2000,300,IF(sum(H5:H7) < 4000,600,900))
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
This worked amazingly. you Rock!
Last edited by AliGW; 07-22-2018 at 05:48 AM.
In future, please make your thread titles more helpful to all.
- Use concise, accurate thread titles.
- Your post title should describe your problem, not your anticipated solution.
- Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
- Responding to a request to change your thread title by doing so is mandatory.
And please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
you're welcome! Since you are new to the forum, you can also thank contributors by clicking on * add reputation below their posts as that is how we advance on this forum.
Also, it's always worth looking at all suggestions, even if you're not going to use them. The best one now might not always be the best one later if criteria change.
Sambo's suggestion is a simple and effective answer to your question, but is exactly what you asked for, an IF OR formula. When you only have 2 or 3 simple criteria then this works fine, but if you have more it can soon get out of hand. If your scale carried on, up to 6000 return 1200, up to 8000, 1500, 10000,1800, etc. Each would require another IF to be added to the formula.
Imagine how the formula would look if you had 100 pairs of values
In the same situation, the method that I suggested will not get any longer (ok, an extra character or 2, but that's about it). All you would need to do is change the first figure (900) to the upper cap if there is one,if there is no cap then you just remove the number and the formula gets shorter.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks