Styjun

Planar regular languages

What are the advantages and disadvantages of tail wheels that cause modern airplanes to not use them?

Can derivatives be defined as anti-integrals?

Why is it called a stateful and a stateless firewall?

Wrong Schengen Visa exit stamp on my passport, who can I complain to?

In what state are satellites left in when they are left in a graveyard orbit?

Read string of any length in C

How do certain apps show new notifications when internet access is restricted to them?

Can a business put whatever they want into a contract?

Why any infinite sequence of real functions can be generated from a finite set through composition?

In what sequence should an advanced civilization teach technology to medieval society to maximize rate of adoption?

Is it appropriate to CC a lot of people on an email

Why don't Wizards use wrist straps to protect against disarming charms?

Can I see Harvest moon in India?

Meaning of Swimming their horses

Shouldn't countries like Russia and Canada support global warming?

Masking out non-linear shapes on canvas

Are there objective criteria for classifying consonance v. dissonance?

Asked to Not Use Transactions and to Use A Workaround to Simulate One

How to be sure services and researches offered by the University are not becoming cases of unfair competition?

Test to know when to use GLM over Linear Regression?

Is the Dodge action perceptible to other characters?

Why is the car dealer insisting on a loan instead of cash?

Output a Super Mario Image

Getting Excel to select from one of three range options

How to avoid using Select in Excel VBADynamic chart range using INDIRECT: That function is not valid (despite range highlighted)Need help converting a range of numbers to text in excelEXCEL matching elements in a discontinuous rangeNeed help on getting specific information from an excel formulaMatch Columns in different sheets and Update column in ExcelExcel Formula to check if someone's name is in another column and their IP address is not recognisedExcel Formula - If this then that - average over a rangeInterpolate between integer values that can dynamically change and output to a columnCount IF multiple critera in multiple cells

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I'm trying to devise a dynamic spread sheet involving pension contribution percentages which increases with the age of the employees.
I've used the =now() function to calculate their ages on a dynamic basis and I now need to get excel to look at their age in cell H2 and apply the following criteria, altering automatically as their age increases into the next bracket:

• if they are currently aged between 18 - 39 pension contribution is 6%


• between 40 - 49 it is 7%


• and over 50 it is 10%

The formula I've devised is picking up the correct percentages for those 39 & under and for those 50+ but I can't get it to recognize the 7% for those between 40 - 49.

Can anybody tell me where I'm going wrong?

=IF(H2>=OR18<=39,"6%",IF(H2>=OR40<=49,"7%",IF(H2>=OR50>100,"10%")))

excel excel-formula

share|improve this question

edited Mar 28 at 15:20

Raad

3,40011 gold badge1818 silver badges3838 bronze badges

asked Mar 28 at 12:09

Doug BrownDoug Brown

311 bronze badge

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm a bit puzzled by the OR bit in the formula - if you have OR18 Excel assumes it to be a cell reference o.O - no matter, I have suggested an answer that works
  
  – Raad
  Mar 28 at 15:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Great, glad it helped - if you can accept my answer I can get some much needed street cred!
  
  – Raad
  Mar 28 at 15:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi Raad - your solution worked perfectly! Do I need to post a comment anywhere else on the site in order for you to get the 'street cred' you deserved?
  
  – Doug Brown
  Apr 1 at 15:31
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi Doug - just click on the check mark beside the answer to toggle it from greyed out to filled in, thanks.
  
  – Raad
  Apr 5 at 11:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  don't forget to click the tick/check mark! Thanks.
  
  – Raad
  Apr 9 at 14:48
  

  
  
  
  

add a comment
 | 

0

I'm trying to devise a dynamic spread sheet involving pension contribution percentages which increases with the age of the employees.
I've used the =now() function to calculate their ages on a dynamic basis and I now need to get excel to look at their age in cell H2 and apply the following criteria, altering automatically as their age increases into the next bracket:

• if they are currently aged between 18 - 39 pension contribution is 6%


• between 40 - 49 it is 7%


• and over 50 it is 10%

The formula I've devised is picking up the correct percentages for those 39 & under and for those 50+ but I can't get it to recognize the 7% for those between 40 - 49.

Can anybody tell me where I'm going wrong?

=IF(H2>=OR18<=39,"6%",IF(H2>=OR40<=49,"7%",IF(H2>=OR50>100,"10%")))

excel excel-formula

share|improve this question

edited Mar 28 at 15:20

Raad

3,40011 gold badge1818 silver badges3838 bronze badges

asked Mar 28 at 12:09

Doug BrownDoug Brown

311 bronze badge

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm a bit puzzled by the OR bit in the formula - if you have OR18 Excel assumes it to be a cell reference o.O - no matter, I have suggested an answer that works
  
  – Raad
  Mar 28 at 15:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Great, glad it helped - if you can accept my answer I can get some much needed street cred!
  
  – Raad
  Mar 28 at 15:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi Raad - your solution worked perfectly! Do I need to post a comment anywhere else on the site in order for you to get the 'street cred' you deserved?
  
  – Doug Brown
  Apr 1 at 15:31
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi Doug - just click on the check mark beside the answer to toggle it from greyed out to filled in, thanks.
  
  – Raad
  Apr 5 at 11:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  don't forget to click the tick/check mark! Thanks.
  
  – Raad
  Apr 9 at 14:48
  

  
  
  
  

add a comment
 | 

I'm trying to devise a dynamic spread sheet involving pension contribution percentages which increases with the age of the employees.
I've used the =now() function to calculate their ages on a dynamic basis and I now need to get excel to look at their age in cell H2 and apply the following criteria, altering automatically as their age increases into the next bracket:

• if they are currently aged between 18 - 39 pension contribution is 6%


• between 40 - 49 it is 7%


• and over 50 it is 10%

The formula I've devised is picking up the correct percentages for those 39 & under and for those 50+ but I can't get it to recognize the 7% for those between 40 - 49.

Can anybody tell me where I'm going wrong?

=IF(H2>=OR18<=39,"6%",IF(H2>=OR40<=49,"7%",IF(H2>=OR50>100,"10%")))

excel excel-formula

share|improve this question

edited Mar 28 at 15:20

Raad

3,40011 gold badge1818 silver badges3838 bronze badges

asked Mar 28 at 12:09

Doug BrownDoug Brown

311 bronze badge

share|improve this question

edited Mar 28 at 15:20

Raad

3,40011 gold badge1818 silver badges3838 bronze badges

asked Mar 28 at 12:09

Doug BrownDoug Brown

311 bronze badge

share|improve this question

edited Mar 28 at 15:20

Raad

3,40011 gold badge1818 silver badges3838 bronze badges

asked Mar 28 at 12:09

Doug BrownDoug Brown

311 bronze badge

edited Mar 28 at 15:20

Raad

3,40011 gold badge1818 silver badges3838 bronze badges

edited Mar 28 at 15:20

Raad

3,40011 gold badge1818 silver badges3838 bronze badges

asked Mar 28 at 12:09

Doug BrownDoug Brown

311 bronze badge

asked Mar 28 at 12:09

Doug BrownDoug Brown

311 bronze badge

1 Answer
1

active

oldest

votes

0

Hi and welcome to Stack Overflow!

So assuming the value to test is in cell A1, the basic formula for matching within a range (say 1-10) is:

=IF(AND(A1 >= 1, A1 <= 10), "In Range", "Out of Range")

So expanding this to 3 ranges (18-39, 40-49, and 50+), and substituting your percentages, we get:

=IF(AND(A1 >= 18, A1 <= 39), "6%", IF(AND(A1 >= 40, A1 <= 49), "7%", IF(A1 >= 50, "10%")))

So a loose end that needs tying up is what to do if the age is less than 18 - currently this formula will produce FALSE. You might want to put something else in by adding a result for the second condition in the last test - so where the value doesn't match >= 50, e.g.

... IF(A1 >= 50, "10%", "NOT APPLICABLE") ...

or some other value that's appropriate.

share|improve this answer

answered Mar 28 at 15:18

RaadRaad

3,40011 gold badge1818 silver badges3838 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That's brilliant Raad, thank you so much. I don't need to worry about less than 18 as we don't employ anybody under that age
  
  – Doug Brown
  Mar 28 at 15:37
  

  
  
  
  

add a comment
 | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55397313%2fgetting-excel-to-select-from-one-of-three-range-options%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Hi and welcome to Stack Overflow!

So assuming the value to test is in cell A1, the basic formula for matching within a range (say 1-10) is:

=IF(AND(A1 >= 1, A1 <= 10), "In Range", "Out of Range")

So expanding this to 3 ranges (18-39, 40-49, and 50+), and substituting your percentages, we get:

=IF(AND(A1 >= 18, A1 <= 39), "6%", IF(AND(A1 >= 40, A1 <= 49), "7%", IF(A1 >= 50, "10%")))

So a loose end that needs tying up is what to do if the age is less than 18 - currently this formula will produce FALSE. You might want to put something else in by adding a result for the second condition in the last test - so where the value doesn't match >= 50, e.g.

... IF(A1 >= 50, "10%", "NOT APPLICABLE") ...

or some other value that's appropriate.

share|improve this answer

answered Mar 28 at 15:18

RaadRaad

3,40011 gold badge1818 silver badges3838 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That's brilliant Raad, thank you so much. I don't need to worry about less than 18 as we don't employ anybody under that age
  
  – Doug Brown
  Mar 28 at 15:37
  

  
  
  
  

add a comment
 | 

0

Hi and welcome to Stack Overflow!

So assuming the value to test is in cell A1, the basic formula for matching within a range (say 1-10) is:

=IF(AND(A1 >= 1, A1 <= 10), "In Range", "Out of Range")

So expanding this to 3 ranges (18-39, 40-49, and 50+), and substituting your percentages, we get:

=IF(AND(A1 >= 18, A1 <= 39), "6%", IF(AND(A1 >= 40, A1 <= 49), "7%", IF(A1 >= 50, "10%")))

So a loose end that needs tying up is what to do if the age is less than 18 - currently this formula will produce FALSE. You might want to put something else in by adding a result for the second condition in the last test - so where the value doesn't match >= 50, e.g.

... IF(A1 >= 50, "10%", "NOT APPLICABLE") ...

or some other value that's appropriate.

share|improve this answer

answered Mar 28 at 15:18

RaadRaad

3,40011 gold badge1818 silver badges3838 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That's brilliant Raad, thank you so much. I don't need to worry about less than 18 as we don't employ anybody under that age
  
  – Doug Brown
  Mar 28 at 15:37
  

  
  
  
  

add a comment
 | 

Hi and welcome to Stack Overflow!

So assuming the value to test is in cell A1, the basic formula for matching within a range (say 1-10) is:

=IF(AND(A1 >= 1, A1 <= 10), "In Range", "Out of Range")

So expanding this to 3 ranges (18-39, 40-49, and 50+), and substituting your percentages, we get:

=IF(AND(A1 >= 18, A1 <= 39), "6%", IF(AND(A1 >= 40, A1 <= 49), "7%", IF(A1 >= 50, "10%")))

So a loose end that needs tying up is what to do if the age is less than 18 - currently this formula will produce FALSE. You might want to put something else in by adding a result for the second condition in the last test - so where the value doesn't match >= 50, e.g.

... IF(A1 >= 50, "10%", "NOT APPLICABLE") ...

or some other value that's appropriate.

share|improve this answer

answered Mar 28 at 15:18

RaadRaad

3,40011 gold badge1818 silver badges3838 bronze badges

share|improve this answer

answered Mar 28 at 15:18

RaadRaad

3,40011 gold badge1818 silver badges3838 bronze badges

answered Mar 28 at 15:18

RaadRaad

3,40011 gold badge1818 silver badges3838 bronze badges

answered Mar 28 at 15:18

RaadRaad

3,40011 gold badge1818 silver badges3838 bronze badges