Question description:
This user has given permission to use the problem statement for this
blog.
Hi, I need a way to use conditional formatting to make all the cells blank when the top shows "Game End"
Solved by T. F. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
31/08/2018 - 11:31
Hello, Welcome to Got it Pro.
User
31/08/2018 - 11:31
Hi
User
31/08/2018 - 11:31
I need a way to use conditional formatting to make all the cells below Game End to appear empty
Excelchat Expert
31/08/2018 - 11:31
I can see you adding some data on the right side if the screen
Excelchat Expert
31/08/2018 - 11:32
Ok
User
31/08/2018 - 11:32
the results will look like the one on the right
Excelchat Expert
31/08/2018 - 11:32
Are you going to type the Game End in that cell?
User
31/08/2018 - 11:33
which cell?
Excelchat Expert
31/08/2018 - 11:33
C4 D4?
User
31/08/2018 - 11:33
Yes in a way, I have a IF formula which will let them be "Game End" if the condition is met
User
31/08/2018 - 11:34
i just need a way to let those cells below Game End appear to be empty whenever the conditions of my IF formula are met and they show up as "Game End"
Excelchat Expert
31/08/2018 - 11:34
So we can use another if for Game End if the cell is fixed otherwise we can use conditional formating
Excelchat Expert
31/08/2018 - 11:34
Give me a 2 mins
User
31/08/2018 - 11:36
I would prefer to use conditional formatting so my formula doesnt get too long
Excelchat Expert
31/08/2018 - 11:36
ok
Excelchat Expert
31/08/2018 - 11:37
Sorry but we can not handle this with conditional formatting, I just realized numbers could be same and anywhere in the range.
Excelchat Expert
31/08/2018 - 11:38
we have to handle this with if condition
User
31/08/2018 - 11:38
what do you mean by numbers could be the same and anywhere in the range?
Excelchat Expert
31/08/2018 - 11:39
like 500, 100 can come above the Game End and Below the Game End
Excelchat Expert
31/08/2018 - 11:39
There is no logic that we can build inside conditional formatting for range of cells.
Excelchat Expert
31/08/2018 - 11:40
Can you forward me your working sheet?
Excelchat Expert
31/08/2018 - 11:41
=IF($F$4="Game End","")
Excelchat Expert
31/08/2018 - 11:41
something like this, we have to use.
User
31/08/2018 - 11:41
I know that method
User
31/08/2018 - 11:41
But i am only able to populate 1 line of empty rows after "Game End"
Excelchat Expert
31/08/2018 - 11:41
How are you populating one line?
Excelchat Expert
31/08/2018 - 11:42
May be I can help in there
Excelchat Expert
31/08/2018 - 11:42
to develop the logic
Excelchat Expert
31/08/2018 - 11:42
for other rows
User
31/08/2018 - 11:42
okay
User
31/08/2018 - 11:42
can you see theres a empty row after "Game End"?
Excelchat Expert
31/08/2018 - 11:42
I can see
User
31/08/2018 - 11:42
but the following rows are not blank
User
31/08/2018 - 11:42
can you help with that?
Excelchat Expert
31/08/2018 - 11:42
but there is no formula in there
Excelchat Expert
31/08/2018 - 11:43
how one line is empty?
User
31/08/2018 - 11:43
oh
User
31/08/2018 - 11:43
you cant see the formula ?
User
31/08/2018 - 11:43
give me a sec
User
31/08/2018 - 11:43
anyway to copy from excel and paste it here with the formulas?
Excelchat Expert
31/08/2018 - 11:43
if you copy and paste formula wont copy here. I cant see the formula
Excelchat Expert
31/08/2018 - 11:43
you need to copy the formula from excel cell and past ehere
User
31/08/2018 - 11:44
okay i will send you the excel file
[Uploaded an Excel file]
Excelchat Expert
31/08/2018 - 11:45
Let me download and see
User
31/08/2018 - 11:45
ok
User
31/08/2018 - 11:45
i need all the following cells after the blank row to be blank as well
Excelchat Expert
31/08/2018 - 11:46
Ok
Excelchat Expert
31/08/2018 - 11:48
F12<C$2,F12,C$2
Excelchat Expert
31/08/2018 - 11:48
Do you need this logic after game end ?
Excelchat Expert
31/08/2018 - 11:48
Row 9 should not populate anything
Excelchat Expert
31/08/2018 - 11:48
that logic is on your last if statement in the formula
User
31/08/2018 - 11:49
is that logic redundant?
User
31/08/2018 - 11:49
in your opinion
User
31/08/2018 - 11:49
if i am still able to produce the same results, I am ok with removing it
Excelchat Expert
31/08/2018 - 11:50
I am not sure how the game works but once you get the Game End from your current logic .. all columns will be game end and the data below it should be blank.
Excelchat Expert
31/08/2018 - 11:51
let me understand the logic and I will build it.
User
31/08/2018 - 11:51
ok whatever you said make sense
User
31/08/2018 - 11:51
can you test it and check if I will still get the same result?
Excelchat Expert
31/08/2018 - 11:51
Let me see
User
31/08/2018 - 11:53
ok
User
31/08/2018 - 11:53
Basically the game will end when the current balance = 0
User
31/08/2018 - 11:54
and after that, the next row will all appear as "Game End"
Excelchat Expert
31/08/2018 - 11:54
what is it is less then the bet amount?
Excelchat Expert
31/08/2018 - 11:54
if it is*
User
31/08/2018 - 11:54
If the current balance is less than the bet amount, I will bet whatever I have
User
31/08/2018 - 11:55
Eg: If i only have $50 left, but bet amount is $100, I will bet everything I have, which is $50
Excelchat Expert
31/08/2018 - 11:55
still game will continue with current balance, right? it wont end
User
31/08/2018 - 11:55
yes, game will only end when current balance = 0
Excelchat Expert
31/08/2018 - 11:56
ok
User
31/08/2018 - 12:00
Will I be able to extend the session for a 2nd time?
Excelchat Expert
31/08/2018 - 12:00
yeah
User
31/08/2018 - 12:00
okay, thanks!
Excelchat Expert
31/08/2018 - 12:00
four columns are working fine
Excelchat Expert
31/08/2018 - 12:01
i made them blank only that one logic is creating little issues and one col that if F is need to be fixed
Excelchat Expert
31/08/2018 - 12:01
stay tuned.
User
31/08/2018 - 12:01
okay, sure!
User
31/08/2018 - 12:11
Hey im sorry, theres a mistake in my formula for net balance
User
31/08/2018 - 12:11
the $C$3 at the last part of the formula for net balance
User
31/08/2018 - 12:12
oh wait, nvm its okay
User
31/08/2018 - 12:12
i saw wrongly
Excelchat Expert
31/08/2018 - 12:12
That colm is fine
Excelchat Expert
31/08/2018 - 12:12
Current Balance is having a little issues.
User
31/08/2018 - 12:12
Ahh I see
Excelchat Expert
31/08/2018 - 12:14
Actually you have used Random function and that can last the game for any number of times
Excelchat Expert
31/08/2018 - 12:14
so when I handle the current balance
User
31/08/2018 - 12:14
The question only wants 40 rounds actually
Excelchat Expert
31/08/2018 - 12:14
the game goes on for more than 40 rows
User
31/08/2018 - 12:15
the game will end when 40 rounds are completed OR current balance = 0 and game ends prematurely
Excelchat Expert
31/08/2018 - 12:15
[Uploaded an Excel file]
User
31/08/2018 - 12:15
those are the conditions of the question unfortunately
Excelchat Expert
31/08/2018 - 12:15
Look at this sheet,
Excelchat Expert
31/08/2018 - 12:15
Col C D E G are working fine as you want
User
31/08/2018 - 12:17
what is the issue with the current balance column?
Excelchat Expert
31/08/2018 - 12:17
In F column.. with your current logic, it is looking the row above it for game end otherwise mathematical calculation is there. For example: F8 cell is checking the C7 and F7 for game end or 0
Excelchat Expert
31/08/2018 - 12:17
right?
User
31/08/2018 - 12:18
Yes, you are right
Excelchat Expert
31/08/2018 - 12:19
so within this logic, if I say both C7 and F7 are Game End then it should be blank.. which is the condition in row 12 for now and once both are game end then it should result as blank.
Excelchat Expert
31/08/2018 - 12:19
right?
User
31/08/2018 - 12:20
Yes
Excelchat Expert
31/08/2018 - 12:20
I incorporated that logic (see F6 cell) but as soon as I drag it your random function results are going more than 40 rows
User
31/08/2018 - 12:20
the basic concept is actually only once current balance = 0, the next row will all appear as "Game End"
User
31/08/2018 - 12:21
actually that doesnt have to be the logic if you can think of another logic thats fine too
User
31/08/2018 - 12:21
as long as the results are the same , I am okay with it
Excelchat Expert
31/08/2018 - 12:21
Yeah but you dont know when the game will end it could take place anytime after first bet.. so we need to handle the case for every row.. and the only logic goes with this is both C and F should be blank
Excelchat Expert
31/08/2018 - 12:21
Yeah trying
User
31/08/2018 - 12:22
okay
Excelchat Expert
31/08/2018 - 12:22
Logic is working fine but random function is creating problem.
User
31/08/2018 - 12:22
just a side thought: will I be able to use conditional formatting to make cells that have error in them to appear as empty?
Excelchat Expert
31/08/2018 - 12:23
Conditional formatting doesnt change value of a cell but changes the format of the cell
User
31/08/2018 - 12:23
i see, but what if the cell doesnt need to actually empty but instead appear empty? (using white font)
User
31/08/2018 - 12:23
that would be my very last resort
Excelchat Expert
31/08/2018 - 12:24
There isn't a logic to conditionally format
User
31/08/2018 - 12:24
I see, forget about that then
Excelchat Expert
31/08/2018 - 12:24
it is like once something is appeared make all rows after that like this that is not possible but in my current sheet that I sent you.. we can format the #Value may be
User
31/08/2018 - 12:25
yeah I think i get what you mean I faced the same problem when I was trying too
Excelchat Expert
31/08/2018 - 12:26
No you can not handle this with random function in your current logic because I tried a very easy way. Look at my current sheet all columns are working fine. Now, just need to handle the col F, so within my current sheet I gave a logic, if col E is blank then blank otherwise continue... that logic also taking the rows to more than 40
User
31/08/2018 - 12:27
so is there really no way to do this?
Excelchat Expert
31/08/2018 - 12:27
where is the logic for only 40 rows?
User
31/08/2018 - 12:27
the game only last 40 rounds
User
31/08/2018 - 12:27
that is the condition set by the question
Excelchat Expert
31/08/2018 - 12:28
yeah but that logic is not here in this sheet
User
31/08/2018 - 12:28
i only imputted round 1 to 40
User
31/08/2018 - 12:28
because there is only 40 rounds
User
31/08/2018 - 12:28
the timer is ending, will i be able to extend the timer again?
User
31/08/2018 - 12:29
can you contact me on email? soulfunk25@yahoo.com.sg
User
31/08/2018 - 12:29
i am willing to pay for the extra time
Excelchat Expert
31/08/2018 - 12:30
you extended twice?
User
31/08/2018 - 12:30
yes
Excelchat Expert
31/08/2018 - 12:30
its been 60 minutes?
User
31/08/2018 - 12:30
yes
User
31/08/2018 - 12:30
time passes fast :(
User
31/08/2018 - 12:30
can you contact me via my email?
Excelchat Expert
31/08/2018 - 12:30
oops, give the 5 star rating and raise it again, i will accept
User
31/08/2018 - 12:30
oh
Excelchat Expert
31/08/2018 - 12:30
M sorry not allowed to contact outside
User
31/08/2018 - 12:30
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.
