1

So, what i am trying to accomplish here is pretty straight forward, i have a column fare in my spreadsheet and i want to add anew column that would say if the fare is <10 ,10<20,20<30,30+

I came up with this solution but it seems like it is not using a shortcut condition, is there a case statement or any other method i can use to achieve what i want?

=if(J19<10,"<10",IF(AND(J19>10,J19<20),"10<20",IF(AND(J19>20,J19<30),"20<30","30+" )))

fenec
  • 5,637
  • 10
  • 56
  • 82

2 Answers2

2

Try something like this:

=ROUNDDOWN(A1,-1)&"<"&ROUNDUP(A1,-1)
Stepan1010
  • 3,136
  • 1
  • 16
  • 21
1

Since the conditions in a nested set of if functions are evaluated consecutively, you do not need to repeat previous conditions using and. Also note that your original formula doesn't do the right thing at the borderlines, e.g. if J19=10.

So although Excel does not have a case function, the following is simpler and more accurate than your original:

=if(J19<10,"<10",IF(J19<20,"10<20",IF(J19<30,"20<30","30+")))
MattClarke
  • 1,647
  • 1
  • 11
  • 32
  • your formula doesn't work, when J19=7 the result is +30 – fenec Sep 08 '14 at 20:45
  • 1
    Please check if J19 contains the number 7 or the text "7". – MattClarke Sep 09 '14 at 00:16
  • it does contain the number 7 and not the text "7" – fenec Sep 10 '14 at 18:16
  • 1
    There is something weird happening then. It is a straight-forward formula that works fine for me. Can you upload a copy of the spreadsheet somewhere I can take a look? – MattClarke Sep 10 '14 at 23:12
  • here it is https://docs.google.com/spreadsheets/d/1udSgyJfy9M7gGgzDnRgC1NudDb7VQmiSk1R4XG15fs8/edit#gid=499778724 – fenec Sep 12 '14 at 17:15
  • Sorry for the time delay. The problem is that your formula in that spreadsheet is off by one row. e.g. the formula in N2 is testing the value in J1, where it should be testing J2. – MattClarke Sep 15 '14 at 23:15