# If then question in excel

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...
Microsoft Excel

Last Comment
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
SOLUTION
Saqib Husain

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
yobri

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.
barry houdini

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

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
barry houdini

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.

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?
Saqib Husain

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
Saqib Husain

Saqib Husain

Are you interested in a macro for this?

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?

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
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY