0

I am comparing apartments and I have columns for features like bedroom, full bath, half bath, rent, pet friendly, balcony, laundry, utilities, parking, and bus access. Some of these have numbers like bathrooms and rent, while other like pet friendly and utilities have words like "yes" and "Included". I found some help assigning value and so far I have:

=IF(AND(B2>=1, B2<= 1), 1,
 IF(AND(B2>=2, B2<= 2), 2,
 IF(AND(B2>=3, B2<= 3), 3,
 IF(AND(B2>=4, B2<= 4), 4,
 IF(AND(C2>=1, C2<= 1), 1,
 IF(AND(C2>=2, C2<= 2), 2,
 IF(AND(C2>=3, C2<= 3), 3,
 IF(AND(C2>=4, C2<= 4), 4,
 IF(AND(D2>=1, D2<= 1), 1,
 IF(AND(D2>=2, D2<= 2), 2,
 IF(AND(D2>=3, D2<= 3), 3,
 IF(AND(D2>=4, D2<= 4), 4,
 IF(AND(F2>=0, F2<= 1000), 3,
 IF(AND(F2>=1001, F2<= 1500), 2,
 IF(AND(F2>=1501, F2<= 2000), 3,
 IF(AND(G2="Yes"),1, 
 IF(AND(G2="No"),0,(H2="Yes"),1, 
 IF(AND(H2="No"),0,
 IF(AND(I2="Facility"),0,
 IF(AND(I2="Private"),1,
 IF(AND(J2="Included"),1,
 IF(AND(J2="Not Included"),0,(K2="Yes"),1, 
 IF(AND(K2="No"),0,(L2="Yes"),1, 
 IF(AND(L2="No"),0,))))))))))))))))))))))))))

How do I add the rows up with these parameters to create a score for each apartment?

screenshot of my sheet

  • 1
    Why are you doing the strange AND(X>=N,X<=N) instead of simply X=N? – fixer1234 Jan 18 at 19:10
0

paste this formula into O2 cell and then drag it down:

=SUM(B2, C2, D2, IF(AND(F2>0,     F2<1000), 3,
                 IF(AND(F2>=1000, F2<1500), 2,
                 IF(AND(F2>=1500, F2<2000), 3, ))), IF(G2="yes",      1, ),
                                                    IF(H2="yes",      1, ),
                                                    IF(I2="private",  1, ),
                                                    IF(J2="included", 1, ),
                                                    IF(K2="yes",      1, ),
                                                    IF(L2="yes",      1, ))

2

  • No explanation? – user55949 Jan 30 at 10:07
  • @JanDoggen I can add one if you need so. formula sums these cells: B2+C2+D2+F2+G2+H2+I2+J2+K2+L2. this is equivalent to 3+2+1+3+1+0+1+0+0+1 which is 12. those IF chunks acts like switches... if they are true they counts for given value (3, 2, 3, 1, etc.) if they are not resulting in true they counts for 0 eg if cell L2=yes count 1 if L2 is not "yes" count 0. – user0 Jan 30 at 13:25
0

This could be much shorter:

=B2+C2+D2+IFS(F2<=1000,1,F2<=1500,2,TRUE,3)+(I2="Private")+(J2="Included")+ COUNTIF(G2:L2,"Yes")

And I assumed that rent up to 1000 should be a value of 1 not 3, based on the other rent values; but if that really should be a 3, just change it in the formula.

Your Answer

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