Excel - IF Function Problem - Expert Solution

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

I need help with my data validation and nested if formulas
Solved by D. C. in 19 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 05/09/2018 - 04:59
Greetings! Thank you for choosing Got It Pro!
Excelchat Expert 05/09/2018 - 04:59
So, you need help with Data Validation and Nested IFs?
User 05/09/2018 - 04:59
hi give me a sec
User 05/09/2018 - 04:59
i have a file
Excelchat Expert 05/09/2018 - 04:59
Sure go ahead.
User 05/09/2018 - 05:01
[Uploaded an Excel file]
User 05/09/2018 - 05:01
in that file, what i want to do is when the bet size: Other Amounts, I want the bet amount to be based on the K4
User 05/09/2018 - 05:02
and the conditions are: If i get a tail, i will double my bet amount for the next round
User 05/09/2018 - 05:02
and if i get a head, i will bet the initial bet size
Excelchat Expert 05/09/2018 - 05:02
Hmm, so in what cell should the formula be?
User 05/09/2018 - 05:02
can you help me modify my formula inJ8
User 05/09/2018 - 05:02
such that the above conditions are met
Excelchat Expert 05/09/2018 - 05:03
Okay, let me clarify the conditions again as per my understanding.
Excelchat Expert 05/09/2018 - 05:03
First, if the bet size = other amounts, bet amount should be K4. Yes?
User 05/09/2018 - 05:03
I have almost finished the formula already, I just cant get it the double when I get a Tail for Coin Toss
User 05/09/2018 - 05:03
yes correct
User 05/09/2018 - 05:03
hold on for a moment
Excelchat Expert 05/09/2018 - 05:03
Okay sure
User 05/09/2018 - 05:04
just want to check with you if it is possible for me to use data valdiation (listing)
User 05/09/2018 - 05:04
but at the same time allow the user to input their own values as well
User 05/09/2018 - 05:04
that means currently the option is $10,50,100
User 05/09/2018 - 05:04
is it possible for other users to input their own values such as $250?
Excelchat Expert 05/09/2018 - 05:05
So your data validation is optiosn + a custom number?
Excelchat Expert 05/09/2018 - 05:05
options*?
User 05/09/2018 - 05:05
Yes
User 05/09/2018 - 05:05
is it possible to do that?
Excelchat Expert 05/09/2018 - 05:05
hold on
User 05/09/2018 - 05:05
ok
Excelchat Expert 05/09/2018 - 05:07
Yes, we can do that.
User 05/09/2018 - 05:07
how do i do that?\
Excelchat Expert 05/09/2018 - 05:07
So what should we do first?
Excelchat Expert 05/09/2018 - 05:08
Nested IF or data validation?
User 05/09/2018 - 05:08
the data validation
Excelchat Expert 05/09/2018 - 05:08
Okay, in what cell do you want it to be?
User 05/09/2018 - 05:08
if i can do that, i have an old version of formula which should work perfectly
User 05/09/2018 - 05:08
J4
Excelchat Expert 05/09/2018 - 05:08
Okay hold on.
User 05/09/2018 - 05:08
can u see the list ?
Excelchat Expert 05/09/2018 - 05:09
Where is the list?
User 05/09/2018 - 05:09
i have already input 10,50,100
User 05/09/2018 - 05:09
a drop down list at cell J4
Excelchat Expert 05/09/2018 - 05:09
No, there's nothing.
User 05/09/2018 - 05:09
oh okay must be some error when i made a duplicate
Excelchat Expert 05/09/2018 - 05:10
In any case, that's fine.
User 05/09/2018 - 05:10
but can you help be create the list to be $10, $50 and $100?
Excelchat Expert 05/09/2018 - 05:10
Sure, got it.
User 05/09/2018 - 05:10
as well as allow the user to input any value that they want
Excelchat Expert 05/09/2018 - 05:10
Here you go.
[Uploaded an Excel file]
Excelchat Expert 05/09/2018 - 05:10
The list is in a different sheet named "Bets"
Excelchat Expert 05/09/2018 - 05:10
You can test it out, the drop down would how 10, 50 and 100.
Excelchat Expert 05/09/2018 - 05:11
But you can directly edit it
Excelchat Expert 05/09/2018 - 05:11
And there would be no errors.
User 05/09/2018 - 05:11
ok
User 05/09/2018 - 05:11
let me check
Excelchat Expert 05/09/2018 - 05:11
sure.
User 05/09/2018 - 05:13
yup that works
User 05/09/2018 - 05:13
can you tell me how did u manage to do it?
Excelchat Expert 05/09/2018 - 05:13
Okay. Sure!
Excelchat Expert 05/09/2018 - 05:13
So it's actually really simple. Just do the data validation.
Excelchat Expert 05/09/2018 - 05:13
You'd be selecting list right? Then selecting your list.
User 05/09/2018 - 05:14
yes, but how do i still allow the cells to be edited to other values?
Excelchat Expert 05/09/2018 - 05:14
In the Data Validation window, there would be the "Error Alert" tab. You just have to disable it by unticking the "show error alert" option.
Excelchat Expert 05/09/2018 - 05:14
After that, anything should be fine :)
User 05/09/2018 - 05:15
ok
User 05/09/2018 - 05:15
give me a while to try it on my sheet
Excelchat Expert 05/09/2018 - 05:15
Sure.
User 05/09/2018 - 05:18
okay i got it
User 05/09/2018 - 05:18
that would be all
User 05/09/2018 - 05:18
thanks
Excelchat Expert 05/09/2018 - 05:18
Cool!
Excelchat Expert 05/09/2018 - 05:18
Happy to have helped. Will mark this as answered now!
User 05/09/2018 - 05:18
ok

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.