1

I have the following Excel workbook sheets.

SHEET 1 contains data on criminal sentences handed down for specific individuals:

| Name | DateOfBirth | DateOfSentence | SentenceYears |
|------|-------------|----------------|---------------|
| A    | 01/01/1960  | 04/06/2007     | 30            |
| B    | 21/03/1964  | 12/10/2009     | 25            |

SHEET 2 contains the average life expectancies for people born in specific years:

| 1960 | 1961 | 1962        | 1963           | 1964          |
| 65   | 65   | 66          | 67             | 67            |

I want to create a formula to determine if the sentence imposed (from SHEET 1, column SentenceYears) is like to imprison the person beyond their anticipated life expectancy based on the average figure for someone born in the same year in SHEET 2. If true, return "LIKELY DEAD", if false, return "LIKELY NOT DEAD".

Any assistance would be greatly appreciated! I have no experience with pointing Excel to specific cells based on date matching.

3
  • Sheet2 allows to calculate (year of birth - average year of death). Then compare it with birth date and "predict". – Akina Sep 16 '19 at 13:50
  • Thanks @Akina, but how to tell Excel what the correct column is to look at to find the life expectancy based on the year of the individual's birth? – Craig Sep 16 '19 at 14:01
  • 2
    Look into HLOOKUP. You may also want to consider transposing the data and using VLOOKUP – cybernetic.nomad Sep 16 '19 at 14:17
0

Follow these steps to fix the issue:

enter image description here

How it works:

  • Step 1: Find Age On Sentence Year & Age On Sentence Completed.
  • Formula in Cell F91 in Sheet 1:

    =DATEDIF(B91,C91,"Y")

  • Formula in Cell G91 in Sheet 1:

    =F91+D91

  • Formula in cell H91 in Sheet 1:

=IF(INDEX(Sheet2!A$97:E$97,MATCH(YEAR(B91),Sheet2!A$96:E$96,0))<G91,"Dead","NotDead")

N.B.

  • =INDEX(A$97:E$97,MATCH(YEAR(B91),A$96:E$96,0)) returns 65 for Prisoner A & 67 for B, and both are less than Age On Sentence Completed.
  • Therefore both are likely dead when complete the sentence.

Adjust cell references in the formula as needed.

1
  • Glad to help you @Craig ,, keep asking ☺ – Rajesh S Sep 17 '19 at 3:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.