英文:
What is the correct syntax to classify ages into groups using IF statements in Google Sheets?
问题
=IF (H19<20, "0-19", IF ((H19>=20 AND H19<40), "20-39", IF ((H19>=40 AND H19<60), "40-59", IF ((H19>=60 AND H19<70), "60-69", IF (H19>=70, ">= 70", "WRONG")))))
英文:
[Why am i getting "formula parse error" when I try to classify the ages (column H) into groups using the following formula? And is there a better way? Thanks for your assistance:
=IF (H19<20, “0-19”, IF ((H19>=20 AND H19<40), “20-39”, IF ((H19>=40 AND H19<60), “40-59”, IF ((H19>=60 AND H19<70), “60-69”, IF (H19>=70, ">= 70", “WRONG”)))))
I was expecting to output the Age column into strings based on my category definitions.
答案1
得分: 3
=let(Σ,{0 ,"0-19";
20 ,"20-39";
40 ,"40-59";
60 ,"60-69";
70 ,">=70"},
vlookup(H19,Σ,2,1))
- 使用***VLOOKUP***的近似匹配能力
英文:
Can you try:
=let(Σ,{0 ,"0-19";
20 ,"20-39";
40 ,"40-59";
60 ,"60-69";
70 ,">=70"},
vlookup(H19,Σ,2,1))
- uses VLOOKUP approx. match capability
答案2
得分: 1
The portions that you have formatted as (H19>=20 AND H19<40) should be changed to AND(H19>=20, H19<40). Your final formula should then be:
=IF(H19<20, "0-19",
IF(AND(H19>=20, H19<40), "20-39",
IF(AND(H19>=40, H19<60), "40-59",
IF(AND(H19>=60, H19<70), "60-69",
IF(H19>=70, ">= 70", "WRONG")))))
Alternatively:
=IFS(OR(NOT(ISNUMBER(H19)),H19<0), "WRONG",
H19<20, "0-19",
AND(H19>=20, H19<40), "20-39",
AND(H19>=40, H19<60), "40-59",
AND(H19>=60, H19<70), "60-69",
H19>=70, ">= 70")
英文:
The portions that you have formatted as (H19>=20 AND H19<40) should be changed to AND(H19>=20, H19<40). Your final formula should then be:
=IF(H19<20, “0-19”,
IF(AND(H19>=20, H19<40), “20-39”,
IF(AND(H19>=40, H19<60), “40-59”,
IF(AND(H19>=60, H19<70), “60-69”,
IF(H19>=70, ">= 70", “WRONG”)))))
Alternatively:
=IFS(OR(NOT(ISNUMBER(H19)),H19<0), "WRONG",
H19<20, "0-19",
AND(H19>=20, H19<40), "20-39",
AND(H19>=40, H19<60), "40-59",
AND(H19>=60, H19<70), "60-69",
H19>=70, ">= 70")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论