# If Statements with Ranges

1. ## If Statements with Ranges

Hi there,

I need to calculate some weighted percentages with the following criteria:
If X=70% then I multiply 80% of 30%
If X=75% then I multiply 100% of 30%
If X=80% then I add 10% to the 30%
Anything beyond 80% is at most 40%.

How do I calculate what this percentage will be if say for example, X = 73%? It's expected to be over 80%.
1. If X<70%
2. If 70%<=X<75%
3. If 75%<=X<80%

if X = 69% it will be less than 24% (less than 80% of 30%)
if X = 73%, the percentage will be between 24%-30% (between 80% and 100% of 30%), how can I determine how this is scaled?
if X = 81%, then it will be at most 40%

Thanks bunch!  Register To Reply

2. ## Re: If Statements with Ranges

VLOOKUP can do it  Register To Reply

3. ## Re: If Statements with Ranges Originally Posted by tim201110 VLOOKUP can do it
Can you give an example?

if X = 69% it will be less than 24% (less than 80% of 30%)
if X = 73%, the percentage will be between 24%-30% (between 80% and 100% of 30%), how can I determine how this is scaled?
if X = 81%, then it will be at most 40%  Register To Reply

4. ## Re: If Statements with Ranges

Will you please attach a sample Excel workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.   Register To Reply

5. ## Re: If Statements with Ranges

I first need to know the formula that would calculate these percentages for the conditions I mentioned. The outcomes are stated in the original post.  Register To Reply

6. ## Re: If Statements with Ranges

And we need to see the exact set-up and formatting of the data in question to be able to provide an answer, otherwise there will be an element of guesswork. Help us to help you. A very simple sample workbook will do.   Register To Reply

7. ## Re: If Statements with Ranges

Let me know if this helps.  Register To Reply

8. ## Re: If Statements with Ranges

I didn't look at your sheet, as I think I followed you....

Try this:

=MIN(40,B4*(MIN(80,80*A4/70)+MIN(20,4*MAX(0,A4-70))+2*MAX(0,A4-75)))  Register To Reply

9. ## Re: If Statements with Ranges

Hey Glenn, I think you are on the right track except X=80 should give me 40% (100% of 30% + 10%).

Anything beyond 80%: 81,82,83,100, should give me at most 40%.  Register To Reply

10. ## Re: If Statements with Ranges

Your wording was ambiguous. At 70...80%, at 75... 100% and at 80 (I assumed) 110% (i.e. 33)

=MIN(40,B4*(MIN(80,80*A4/70)+MIN(20,4*MAX(0,A4-70))+20*MAX(0,A4-75)/3))  Register To Reply

11. ## Re: If Statements with Ranges Originally Posted by Glenn Kennedy Your wording was ambiguous. At 70...80%, at 75... 100% and at 80 (I assumed) 110% (i.e. 33)

=MIN(40,B4*(MIN(80,80*A4/70)+MIN(20,4*MAX(0,A4-70))+20*MAX(0,A4-75)/3))
Thank you Glenn.  Register To Reply

12. ## Re: If Statements with Ranges

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.  Register To Reply

13. ## Re: If Statements with Ranges

Can you explain the logic behind what you did?

MIN
{40,B4*
[
MIN(80,80*A4/70)
Why 80*A4/70?
Why take the minimum?
Which 80 corresponds to the factor of 80% multiplied by 30% when it reaches 70%?
Which 80 corresponds to anything beyond the 80%?

I should have used different numbers for my example.

MIN(20,4*MAX(0,A4-70))
What does the 20 represent?
Why is there a factor of 4?
Why the difference

20*MAX(0,A4-75)/3
]
}
Why divide by 3?

See attached.  Register To Reply

14. ## Re: If Statements with Ranges

The formula has 4 parts, one for each condition:
=MIN(40,B4*(MIN(80,80*A4/70)+MIN(20,4*MAX(0,A4-70))+20*MAX(0,A4-75)/3))

Red: If X=70% then I multiply 80% of 30%. multiplies80 by a4 and divids by 70 and returns that value, or 80 - whichever is smaller.

PLUS Green: If X=75% then I multiply 100% of 30%. In the next bit, an increase of 5 in X produces an increase of 20 (100-80) in the number to be multiplied by the %. This bit is only applied at values above 70. So... return the maximum bethween zero and a4-70. Multiply by 4 (being 20/5. Finally return that value or 20 - whichever is smaller.

PLUS Blue. If X=80% then I add 10% to the 30%... Same logic, except that your wording was confused here. What you mean was that the END RESULT would rise to 40%, whereas previously you had talked about the value that was multiplied by 30%. So, what you were really saying was that when A4 rises by a further 5, the number multiplied by 30% should invrease by 33.3333%. 20*5/3=33.33333

Black. then multiply by the percentage (30) and return that value, or 40, whichever is smaller.

That's it....

You're welcome.

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.  Register To Reply

15. ## Re: If Statements with Ranges

Thank you!

If all these cells were formatted as a percentage, the formula will not work.

What changes does the formula need to take into account cells as percentages and not numbers?

Otherwise I just get '40' regardless.  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1