Excel formula (calculation if, else)

Status: Closed
Nagroda: $10
Uzyskane Zgłoszenia: 32
Zwycięzca: jejkop

Winner

Opis Konkursu

The first one whom come up with a working formula wins the competition. The formula should work on MS excel for mac and windows.

I need an Excel formula that does the following:
I have a social security number of a person that is in format YYMMDD-xxxx. Example: 760412-9021. Based on the year (yy), the following calculation is to be made:
If the person was born between 1951 or later, the result shall be 31.42%
If the person is born from 1938 to 1950, the result being 16.36%
If peronsen born in 1937 or earlier, the result shall be 6.15%

Social security number is located in column C and starts in row 6
The result in percent is to be written in column J and starts in row 6

Zalecane Umiejętności

Excel

Winner

Opublikuj Konkurs Taki Jak Ten

Opinie o Pracodawcy

“jejkop did solve a problem that i had in excel. The solution was nice executed! ”

zedig, Sweden.

Publiczna Tablica Wyjaśnień

Organizator Konkursu
- 7 lat temu
Thank you for all the contributions! The first formula that I could get to work is number 36. So thats the Winner of this Contest. Big thank you to all!
- 7 lat temu
Wyświetl 7 wiadomości więcej
1. Organizator Konkursu
  - 7 lat temu
  I have tried all the entrys but the first one that worked was the winner.
  - 7 lat temu
2. cziner
  - 7 lat temu
  OK
  - 7 lat temu
ksinghrajeev
- 7 lat temu
Please check entry #30 and let me know whether you are facing any issue.
- 7 lat temu
irmalampinen
- 7 lat temu
=IF(VALUE(LEFT(C6,2))50,0.3142,0.1636))
- 7 lat temu
irmalampinen
- 7 lat temu
There are so many entries, but anyway here is mine:
- 7 lat temu
cziner
- 7 lat temu
Dear CH, please check my simple solution: #29
- 7 lat temu
pedroivf
- 7 lat temu
i figure out that your Mac need ";" instead of "," in those formulas too. What language do you have in your mac?
- 7 lat temu
Wyświetl 9 wiadomości więcej
1. pedroivf
  - 7 lat temu
  there is a very solution for this. If i can send you a file (withmy english office) when your swedish office open it will convert it automatically to swedish excel file.
  - 7 lat temu
2. pedroivf
  - 7 lat temu
  I installed Sweddish Office 2016, and then solution is this now: "=OM(LÄNGD($C6)>11; 0,3142; OM(TALVÄRDE(VÄNSTER($C6;2))>=51;0,3142;OM(TALVÄRDE(VÄNSTER($C6;2))>=38;0,1636;0,0615)))" , you have to put this formula in the cell J6 and with format "percentage" with 2 decimals. i works perfectly.
  - 7 lat temu
hmuralikrishna9
- 7 lat temu
=IF(LEN(C6)=13,"31.42%",IF(VALUE(LEFT(C6,2))>=51,"31.42%",IF(VALUE(LEFT(C6,2))>=38,"16.36%","6.15%")))
- 7 lat temu
Wyświetl 4 wiadomości więcej
1. hmuralikrishna9
  - 7 lat temu
  Check my entry 14. It should work perfectly. (Format cell as "Percentage" and use the formula)
  - 7 lat temu
2. Organizator Konkursu
  - 7 lat temu
  In swedish mac. I only have a mac to try on right now
  - 7 lat temu
Organizator Konkursu
- 7 lat temu
Error message in excel for mac: NUMBERVALUE(text; [decimalsign]; [thousenddecimal])
- 7 lat temu
1. tonynlm
  - 7 lat temu
  Ha, I see you figure out the error. I did change the same thing before knowing that we have this Clarification Board. Well done my friend :)
  - 7 lat temu
pedroivf
- 7 lat temu
This is the formula in swedish mac office with the correction of "2000" years: "=IF(LEN(C13)>11; 0,3142; IF(VALUE(LEFT($C13;2))>=51;0,3142;IF(VALUE(LEFT($C13;2))>=38;0,1636;0,0615)))"
- 7 lat temu
1. pedroivf
  - 7 lat temu
  correction : "=IF(LEN(C6)>11; 0,3142; IF(NUMBERVALUE(LEFT($C6;2))>=51;0,3142;IF(NUMBERVALUE(LEFT($C6;2))>=38;0,1636;0,0615)))"
  - 7 lat temu
2. pedroivf
  - 7 lat temu
  you have to format the cell where you put the formula as "percent" and with 2 decimals
  - 7 lat temu
ptykamikaze
- 7 lat temu
=IFERROR(IF(DATE(LEFT(C7,2),RIGHT(LEFT(C7,4),2),RIGHT(LEFT(C7,6),2))>=DATE(1951,1,1),31.42,IF(AND(DATE(LEFT(C7,2),RIGHT(LEFT(C7,4),2),RIGHT(LEFT(C7,6),2))>=DATE(1938,1,1),DATE(LEFT(C7,2),RIGHT(LEFT(C7,4),2),RIGHT(LEFT(C7,6),2)) <DATE(1951,1,1)),16.36,IF(DATE(LEFT(C7,2),RIGHT(LEFT(C7,4),2),RIGHT(LEFT(C7,6),2)) <DATE(1938,1,1),6.15,0))),"ERROR")
- 7 lat temu
ptykamikaze
- 7 lat temu
=IFERROR(IF(DATE(LEFT(C9,2),RIGHT(LEFT(C9,4),2),RIGHT(LEFT(C9,6),2))>=DATE(1951,1,1),31.42,IF(AND(DATE(LEFT(C9,2),RIGHT(LEFT(C9,4),2),RIGHT(LEFT(C9,6),2))>=DATE(1938,1,1),DATE(LEFT(C9,2),RIGHT(LEFT(C9,4),2),RIGHT(LEFT(C9,6),2))
- 7 lat temu
Organizator Konkursu
- 7 lat temu
I have a swedish version of office and excel. In sweden we use , as divider and not . The most common error in the submitted formulas is: NUMBERVALUE(text; [decimalsign]; [thousenddecimal])
- 7 lat temu
pedroivf
- 7 lat temu
I realiced that you have another thing in the your Cell C6. For a precise formula we need more examples please. Because for example "2000-04-12-xxxx" and "760412-9021", is very different formula to detect the year, and i think the error is for another reason.
- 7 lat temu
1. Organizator Konkursu
  - 7 lat temu
  Sorry, born after 2000 is 20000412-xxxx
  - 7 lat temu
2. Organizator Konkursu
  - 7 lat temu
  I get formula error and it highlight the first c6 in the formula.
  - 7 lat temu
pedroivf
- 7 lat temu
i have a Mac, btw.
- 7 lat temu
ptykamikaze
- 7 lat temu
:)
- 7 lat temu
ptykamikaze
- 7 lat temu
Mine is using VALUE
- 7 lat temu
pedroivf
- 7 lat temu
in mac you need to use "VALUE" instead of "NUMBERVALUE"
- 7 lat temu
ptykamikaze
- 7 lat temu
My formula takes use dates and not single YY..... Using YY will not working if the customer was born on 2000
- 7 lat temu
pedroivf
- 7 lat temu
Esta es la fórmula "=IF(NUMBERVALUE(LEFT($C6,2))>=51,0.3142,IF(NUMBERVALUE(LEFT($C6,2))>=38,0.1636,0.0615))"
- 7 lat temu
1. pedroivf
  - 7 lat temu
  You have to put it in the cell J6 and add the format of % with 2 decimal digits. I have a cuestion, what happen with people that born after 2000 year, it will appear like "000412-9026" and how do you know that is not 1900?
  - 7 lat temu