# 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.
Asked:
###### Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Technical AdvisorCommented:
Would you please attach an example excel file to show what cells are exactly involved ?
0
System 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
Technical AdvisorCommented:
I wasn't sure what exactly you want.

Please check the attached files.
Test.xlsx
Test-2.xlsx
0
Microsoft Excel ExpertCommented:
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
Author Commented:
worked perfecct.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.