Excel formula (calculation if, else)
- Status: Closed
- Prize: $10
- Entries Received: 32
- Winner: jejkop
Public Clarification Board
-
Contest Holder - 7 years ago
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 years ago
View 7 more messages-
Contest Holder - 7 years ago
I have tried all the entrys but the first one that worked was the winner.
- 7 years ago
-
cziner
- 7 years ago
OK
- 7 years ago
-
ksinghrajeev
- 7 years ago
Please check entry #30 and let me know whether you are facing any issue.
- 7 years ago
-
irmalampinen
- 7 years ago
=IF(VALUE(LEFT(C6,2))50,0.3142,0.1636))
- 7 years ago
-
irmalampinen
- 7 years ago
There are so many entries, but anyway here is mine:
- 7 years ago
-
cziner
- 7 years ago
Dear CH, please check my simple solution: #29
- 7 years ago
-
pedroivf
- 7 years ago
i figure out that your Mac need ";" instead of "," in those formulas too. What language do you have in your mac?
- 7 years ago
-
pedroivf
- 7 years ago
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 years ago
-
pedroivf
- 7 years ago
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 years ago
-
hmuralikrishna9
- 7 years ago
=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 years ago
-
hmuralikrishna9
- 7 years ago
Check my entry 14. It should work perfectly. (Format cell as "Percentage" and use the formula)
- 7 years ago
-
Contest Holder - 7 years ago
In swedish mac. I only have a mac to try on right now
- 7 years ago
-
Contest Holder - 7 years ago
Error message in excel for mac: NUMBERVALUE(text; [decimalsign]; [thousenddecimal])
- 7 years ago
-
tonynlm
- 7 years ago
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 years ago
-
pedroivf
- 7 years ago
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 years ago
-
ptykamikaze
- 7 years ago
=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 years ago
-
ptykamikaze
- 7 years ago
=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 years ago
-
Contest Holder - 7 years ago
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 years ago
-
pedroivf
- 7 years ago
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 years ago
-
Contest Holder - 7 years ago
Sorry, born after 2000 is 20000412-xxxx
- 7 years ago
-
Contest Holder - 7 years ago
I get formula error and it highlight the first c6 in the formula.
- 7 years ago
-
pedroivf
- 7 years ago
i have a Mac, btw.
- 7 years ago
-
ptykamikaze
- 7 years ago
:)
- 7 years ago
-
ptykamikaze
- 7 years ago
Mine is using VALUE
- 7 years ago
-
pedroivf
- 7 years ago
in mac you need to use "VALUE" instead of "NUMBERVALUE"
- 7 years ago
-
ptykamikaze
- 7 years ago
My formula takes use dates and not single YY..... Using YY will not working if the customer was born on 2000
- 7 years ago
-
pedroivf
- 7 years ago
Esta es la fórmula "=IF(NUMBERVALUE(LEFT($C6,2))>=51,0.3142,IF(NUMBERVALUE(LEFT($C6,2))>=38,0.1636,0.0615))"
- 7 years ago
-
pedroivf
- 7 years ago
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 years ago
How to get started with contests
-
Post Your Contest Quick and easy
-
Get Tons of Entries From around the world
-
Award the best entry Download the files - Easy!