 SOLVED

Deleted
Not applicable

I NEED HELP! EXAM TOMORROW

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

Re: I NEED HELP! EXAM TOMORROW

@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.

Re: I NEED HELP! EXAM TOMORROW

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

Re: I NEED HELP! EXAM TOMORROW

@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?

Re: I NEED HELP! EXAM TOMORROW

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%

Re: I NEED HELP! EXAM TOMORROW

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!!

Re: I NEED HELP! EXAM TOMORROW

thank you everyone!!!!!!!