If then question in excel

sharris_glascol
sharris_glascol used Ask the Experts™
on
I am trying to calculate points awarded in an excel file where if a person doesn't have a total weight in column h3 they would get 30 points otherwise it would go off there ranking in column i3 where is would reference a chart for the points..  I am not able to get this to work...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
=if(h2="",30,vlookup(h2,$i$3:$j$20,2))

this is for cell h2 with the assumption that the value in h2 be looked up in I3:I20 and the value in the corresponding row is returned.

If this is not what you are looking for then it would be helpful to see a sample file uploaded here by you.

Saqib
Top Expert 2011

Commented:
From what I understood of the above, will this such formula work?

=IF(H3="","30 points",I3)

Essentially, if H3 is blank (you could amend the parameter here accordingly), then "30 points" will be displayed but if it is not blank (again, you can change the parameter) then display the contents of cell I3.
Most Valuable Expert 2013

Commented:
What does the chart look like - do you mean a sort of lookup table? Maybe use a formula like

=IF(H3="",30,LOOKUP(I3,table))

where table is a two column table with the lower bounds of each weight range in ascending order in the first column and the value to be assigned for those ranges in the second.

regards, barry
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

sharris_glascolIT Manager

Author

Commented:
Ok I am attaching the file, if you goto week1 tab I am trying to get the points awarded to calculate, so that if the total weight is 0 then they will only get 30 pts, but if there is a weight then it will award point via the rank in column I that references the points tab.
Book2.xls
Most Valuable Expert 2013
Commented:
You should be able to use VLOOKUP as Saqib, suggested....or LOOKUP as per my suggestion - they will both work - with LOOKUP you can use this formula in J3 copied down

=IF(H3=0,30,LOOKUP(I3,Points!A$2:B$13)+0)

If you want a blank if H3 is blank change to

=IF(H3="","",IF(H3=0,30,LOOKUP(I3,Points!A$2:B$13)+0))

regards, barry
sharris_glascolIT Manager

Author

Commented:
Now is there an easy way to get this info to summarize from week 1, 2, 3, 4, 5, classic to pull in the names from each of those weeks to sheet 11 except duplicates so I can tally a total from week to week?
You can use

=IF(H3=0,30,--VLOOKUP(I3,Points!$A$2:$B$13,2,0))

and copy down.

To do this for all weeks you can select all those sheets by pressing Shift and then do it on any one sheet it will be replicated on all sheets.

Saqib
Sorry misread your comment 35780047
Are you interested in a macro for this?
sharris_glascolIT Manager

Author

Commented:
So another question I have a Names tab that I want to be able to automatically pull them into a summary tab, but want it to auto update when a new name is added.  I made the names and ID a table will this help?
sharris_glascolIT Manager

Author

Commented:
How do I solve this if then statement.  I need a cell to sum accross tabs if certain cell match each other.  For instance in the summary page I have a cell that contains 1107-01 and then in tab a, b, c, d if they have that cell I want them to add a column in that field with a, b, c, d
Most Valuable Expert 2011
Awarded 2010

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial