SOLVED

I NEED HELP! EXAM TOMORROW

Deleted
Not applicable

I have a question that i need help for an exam tomorrow. I have list of numbers that show the number of games played by NBA players. The questions says that if you play less than 8 games you get a 2% bonus, if you play 8-16 games you get a bonus of 3% and if you play 17-24 games you get a bonus of 4%. I have a list of values (8,13,17,22,28,29). The question wants me to make a dynamic formula that shows what bonus each player will get based on the games they have played. 

7 Replies
best response
Solution

@Deleted 

=IF(B2<8,2,IF(AND(B2>=8,B2<=16),3,IF(AND(B2>=17,B2<=24),4,"")))

 

This can be done with a nested IF formula for example.

 

I tried doing this and only values showed up for the cells that had 8 and 24 in them. I think the problem is that these are ranges of numbers

@Deleted 

I can't imagine what the ranges of numbers look like. Are these ranges in single cells like "0 - 7" and "8 - 16" and "17 - 24".

 

Can you attach a file that shows the data you want to analyse?

The formula should be applied to the list of values (8,13,17,22,28,29) where ever they are.
My question is first what to do with 28 and 29 since you didn't say what happens if they play >24 games. Does the pattern continue +1% every 8 games?
The formula given above (which doesn't account for the higher number of games) =IF(B2<8,2,IF(AND(B2>=8,B2<=16),3,IF(AND(B2>=17,B2<=24),4,""))) could be simplified to =IF(B2<8,2,IF(B2<=16,3,IF(B2<=24,4,""))) since it is a sequential operation the other parts of those AND() statements is redundant.
it can be further simplified to =IFS(B2<8,2,B2<=16,3,B2<=24,4,true,"") using the IFS statement
and alternatively you could 'calculate' the amount: =INT(B2/8)/100+2%
https://1drv.ms/x/s!AnjmDW-OfCa5gRlfNwa65cPXFIOZ?e=ZOWFzc Here is the link to the workbook where i explain everything and you can see my problem in column H!!
thank you everyone!!!!!!!