Reporting Text based on the numbers in two different cells

[MBecker79]

Hello All,

Excel novice here, trying to quickly learn how to embed and nest AND into IF(S) logic in excel (using 365 cloud on PC)

Situation: We have people who are eligible for a reward based on two independent criteria of participation: (1) the quantity of their participation (i.e. the number of their visits) and (2) the consistency of their visits over time (how many weeks they have had a visit(s)). For example, if someone visited 12 times, and those visits occurred over 8 or more weeks, they would be eligible for the maximum reward points, i.e. 8 points. However, if another person visited 12 times, but only over 5 weeks, they would only be eligible for 4 points. Lastly, if someone visited 12 times in one week, they actually would not eligible for any reward points. The chart below shows the two criteria that have to be fulfilled for each level of reward points.

In my sheet the total number of visits is displayed in cells C57 to X57 for each participant, while the number of weeks of participation are displayed in C58 to X58.

Number of Meetings	Number of Weeks with Meetings	Reward Points
12-14	8 weeks or more	8 Points
9-11	6-7 weeks	6 Points
6-8	4-5 weeks	4 Points
3-5	2-3 weeks	2 Points
Less than 3	Fewer than 2 weeks	0 Points

I've been watch youtube videos all day and can't figure this one out. Your help is greatly appreciated.

Many thanks,

MB

 

[MBecker79]

Probably should have added that I'm hoping the value will read "Add 8 Points," "Add 6 Points," "Add 4 Points," "Add 2 Points," and "Did Not Earn Points"

Thanks

 

[etaf]

how about
if( AND( C57>=12, C58>=8) , "add 8 points", if( AND(C57>=9, C58>=6), "add 6 points" ,if( AND(C57>=6, C58>=4), "add 4 points" , if( AND(C57>=3, C58>=2), "add 2 points" , "Did Not Earn Points" ))))

I tend not to use IFS - as it has to evaluate ALL conditions first

however, If this does not work

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[MBecker79]

@etaf
Works Perfectly! THANKS!

I looked over my work and I got kinda of close with one answer (but then started barking up worse trees):

'=IF(AND(C57>11,C58>7),"Add 8% Points",IF(AND(C57>8,C58>5),"Add 6% Points",IF(AND(C57>5,C58>3),"Add 4% Points",IF(AND(C57>2,C58>1),"Add 2% Points","Did Not Earn Points))))

First, I like how you used the more precise >= logic...but was my main mistake not knowing where/how to enter spaces in the formula? :/

Are the spaces before AND required to make it work?

 

[etaf]

no spaces needed - just easier to read

and i used copy and past , once i set the first IF - which is why they have the same spaces

this is directly from EXCEL

=IF(AND(C57>=12,C58>=8),"add 8 points",IF(AND(C57>=9,C58>=6),"add 6 points",IF(AND(C57>=6,C58>=4),"add 4 points",IF(AND(C57>=3,C58>=2),"add 2 points","Did Not Earn Points" ))))

looking at your AND() AND - that should work - as you have used > but 1 number lower
so same thing really

AND(C57>11,C58>7)

so
> 11 - must be 12 or more
> 7 - must be 8 or more - assuming integers only

which means
AND(C57>11,C58>7)
should work the same as
AND(C57>=12,C58>=8)

ETC