Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Hey, hope you are well, I am trying to write a formula that calculates a progressive jackpot, caps at £200 and resets to £20 whenever won. the values would come for a separate column where if we entered £0 it would add £15, if we entered £5 it would add £10 and if we entered £10 it was add £5 to the progressive. and if we ever entered the progressive value it would reset to £20.
Solved by A. C. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 22/06/2018 - 04:35
Welcome to ExcelChat!
User 22/06/2018 - 04:35
Hi, how are you?
Excelchat Expert 22/06/2018 - 04:35
Thank you, I'm good
Excelchat Expert 22/06/2018 - 04:35
Could you show on this sheet how your file should look like?
User 22/06/2018 - 04:36
its basically that, in the amount won column i would add what they won, either £0, £5, £0, Current progressive
Excelchat Expert 22/06/2018 - 04:37
As I understand, you want to have some table, right/
Excelchat Expert 22/06/2018 - 04:37
?
User 22/06/2018 - 04:38
and the Current jackpot would update in response, I made the mistake of thinking yeah this will be easy, then realizing I haven't done excel sums since college lol
User 22/06/2018 - 04:39
no data in the table currently, but thats an example of what would be added
Excelchat Expert 22/06/2018 - 04:39
Sorry, trying to understand the logic first
Excelchat Expert 22/06/2018 - 04:40
Could you maybe fill it with more data? Maybe some case scenario when the result should be one, and when another?
User 22/06/2018 - 04:41
so the progressive (Current Jackpot) starts at £20, in the amount won column it would be £, £5, £10, or whats currently in the progressive
User 22/06/2018 - 04:42
Yes, where you have put the add amounts, thats what i want to go into the current jackpot
Excelchat Expert 22/06/2018 - 04:43
Look, if someone won 0 - the current jackpot is raised by 15, 5 - by 10, 10 - by 5, right?
User 22/06/2018 - 04:43
yes
Excelchat Expert 22/06/2018 - 04:44
what happens when there is another amount of won money?
Excelchat Expert 22/06/2018 - 04:44
or it is impossible?
User 22/06/2018 - 04:44
It's likely I am making it insanely over complicated ... I have been known to do that
Excelchat Expert 22/06/2018 - 04:44
Don't worry, everything is fine, I will ask a lot of questions to make sure I develop the right solution
Excelchat Expert 22/06/2018 - 04:45
So what other results might be in column B?
User 22/06/2018 - 04:45
the only other value that would be added would be the current jackpot, if won, that would then make the amount in the current jackpot change to £20
Excelchat Expert 22/06/2018 - 04:45
so the amount won can be 50, for example?
User 22/06/2018 - 04:46
yes, if that was the current jackpot, it would reset to the current jackpot to £20
Excelchat Expert 22/06/2018 - 04:46
great
User 22/06/2018 - 04:46
Yes :D
Excelchat Expert 22/06/2018 - 04:46
Let me think for a while..
Excelchat Expert 22/06/2018 - 04:47
do you manually type what amount was won?
User 22/06/2018 - 04:47
yes
Excelchat Expert 22/06/2018 - 04:47
but there is one problem
Excelchat Expert 22/06/2018 - 04:47
okay, I will think for a while
User 22/06/2018 - 04:58
I maybe asking a little too much from a single formula :/
Excelchat Expert 22/06/2018 - 04:58
Don't worry, I'm on my way
Excelchat Expert 22/06/2018 - 05:02
I finished
Excelchat Expert 22/06/2018 - 05:02
Please have a look
User 22/06/2018 - 05:03
woo, Seems complex, excellent work, this is why I was no where near close to getting it to work lol
Excelchat Expert 22/06/2018 - 05:04
I love solving tasks like that, so thank you for interesting order
Excelchat Expert 22/06/2018 - 05:05
So grey zone is your settings area
Excelchat Expert 22/06/2018 - 05:06
Yellow zone is your log
Excelchat Expert 22/06/2018 - 05:06
And green is your current jackpot
Excelchat Expert 22/06/2018 - 05:06
Feel free to test it
Excelchat Expert 22/06/2018 - 05:07
Orange area is for manual entry
Excelchat Expert 22/06/2018 - 05:07
Yellow are formulas, which you just extend as your table growth
User 22/06/2018 - 05:09
excellent thank you very much, I'll mention this site to the rest of the guys at work :)
Excelchat Expert 22/06/2018 - 05:10
Great I helped you
Excelchat Expert 22/06/2018 - 05:10
Hope to see you and your friends again
Excelchat Expert 22/06/2018 - 05:10
Have a great day!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.