Excel 2010 how to write formula

I have seven columns in a spread sheet with values.  I need to add a column and based on what  is the total of those 7 columns do the following in the new column
if <20 enter a 1
if20-249 enter a 2
if 250 or up enter a 3

Can someone tell me how to do this.  I have never used a formula. Thanks.
kdschoolAsked:
Who is Participating?
 
AlanConnect With a Mentor ConsultantCommented:
Hi,

If your values are in, say, A1:G1, then enter in any other cell:

=IF(SUM(A1:G1)<20,1,IF(SUM(A1:G1)<250,2,3))

See attached.


Alan.
EE-29068200-Version1.xls
0
 
RaminTechnical AdvisorCommented:
Would you please attach an example excel file to show what cells are exactly involved ?
0
 
RobertSystem AdminCommented:
something like this should work:
=IF(AND(SUM(A1:G1)>19,SUM(A1:G1)<250),"2",IF(SUM(A1:G1)<20,"1",IF(SUM(A1:G1)>249,"3")))


so what this formula is doing (note I used A1 through G1 in the add the sum statement you can change that if your values are in different columns/rows)
1. Start an If statement
2. checks if the value is between 20 and 249 and sets it to "2" if it is
3. checks if the value is less than 20 and if it is then it sets the value to "1"
4. checks if the value is greater than 249 and if so it sets the value to "3"
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
RaminTechnical AdvisorCommented:
I wasn't sure what exactly you want.  

Please check the attached files.
Test.xlsx
Test-2.xlsx
0
 
ProfessorJimJamCommented:
kdschool.

that is too much of overkill with so many AND and IF.

you can use the best feature of VLOOKUP with approximate match.

see attached. how simple that is .  just with one short formula.  =VLOOKUP(D2,$A$2:$B$4,2,1)   or hard coded table inside vlookup  simple as this =VLOOKUP(D2,{0,1;20,2;250,3},2,1)
Book1.xlsb
0
 
kdschoolAuthor Commented:
worked perfecct.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.