• 100% Satisfaction Guarantee
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 2011
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now

# how can i make a score sheet for a card game that calculates

how can i make a score sheet for a card game that calculates the sum of each hand for the players and when the score reaches exactly 152 it automatically becomes 102 otherwise it keeps on adding the scores normally

The-PC-Guy :

The-PC-Guy :

Would you be able to provide a copy of the sheet as you have it so far

The-PC-Guy :

So I can see how you have this set up

The-PC-Guy :

and provide the appropriate formulas

The-PC-Guy :

Would you be able to provide a copy of the sheet as you have it so far

10:40 AM

So I can see how you have this set up

10:40 AM

and provide the appropriate formulas

Customer:

Hi Andrew, Thank you for your help

The-PC-Guy :

good morning, at least it is morning where I am

Customer:

Im sorry but I dont have the sheet with me Im asking this for a friend

The-PC-Guy :

no problem

The-PC-Guy :

let me try to understand exactly what it is you want to do

The-PC-Guy :

you want to sum a column of numbers

The-PC-Guy :

and when the score reaches 152

The-PC-Guy :

you want the total to display 102 instead

The-PC-Guy :

or am I missing something

Customer:

thats correct

The-PC-Guy :

do you want this to be done automatically

The-PC-Guy :

or do you want to have to click a button to reset

The-PC-Guy :

like have a macro

Customer:

automatically

The-PC-Guy :

ok

The-PC-Guy :

try this formula

The-PC-Guy :

and this is just an example

The-PC-Guy :

you can change the cell ranges to suit your needs

The-PC-Guy :

lets say that your scores are in cells B1 - B20

The-PC-Guy :

in any other cell put

The-PC-Guy :

= IF(SUM(B1:B20)=152,102,SUM(B1:B20))

The-PC-Guy :

that should provide the desired behavior

Customer:

ok

The-PC-Guy :

if you get stuck or have further questions let me know

The-PC-Guy :

otherwise

The-PC-Guy :

The-PC-Guy :

thanks

The-PC-Guy :

oops

The-PC-Guy :

there is an extra space after the first =

The-PC-Guy :

it has to be

The-PC-Guy :

=IF(SUM(B1:B20)=152,102,SUM(B1:B20))

The-PC-Guy :

The-PC-Guy :

maybe excel will auto correct that anyway

The-PC-Guy :

just wanted to make sure you had the correct formula

Customer:

thank you that works

The-PC-Guy :

The-PC-Guy :

and have a good day

Customer:

sorry but when i add anything more to the score it adds it to the original 152 instead of the 102 ?!

The-PC-Guy :

ok

The-PC-Guy :

lets backtrack for a minute

The-PC-Guy :

you want to have it add to 152

The-PC-Guy :

then change to 102 and continue to add to the 102

Customer:

yes

The-PC-Guy :

ok

The-PC-Guy :

that may create a loop

The-PC-Guy :

but let me check

Customer:

maybe if i explained the game you will be able to figure this out easier

The-PC-Guy :

ok so let me get this strait

The-PC-Guy :

if it reaches 152

The-PC-Guy :

it goes to 102

The-PC-Guy :

then continues to count

The-PC-Guy :

if it reaches 152 again

The-PC-Guy :

does it then need to go to 152

The-PC-Guy :

i mean 102 again

The-PC-Guy :

inotherwords the total can never be greater than 152?

Customer:

no the score can be greater than 152 but it can't be exactly 152

Customer:

whenever its 152 it goes to 102 and then counts agin from there

The-PC-Guy :

but if its 153 that is ok

Customer:

yes

The-PC-Guy :

i think the issue here is that we are creating an infinite loops

The-PC-Guy :

loop

The-PC-Guy :

and excel hates those

The-PC-Guy :

ok

The-PC-Guy :

maybe what we need is a culumulative sum column

The-PC-Guy :

inotherwords

The-PC-Guy :

the score range will be dynamic

The-PC-Guy :

is this correct

The-PC-Guy :

it wont always be 20 rows for example

Customer:

no

Customer:

it could be any number of rows

The-PC-Guy :

ok

The-PC-Guy :

this is what i mean

The-PC-Guy :

let me try something different then

The-PC-Guy :

and see if it works for you

Customer:

ok

The-PC-Guy :

yes this works

The-PC-Guy :

we will have to do a cumulative sum column

The-PC-Guy :

let me send you an example

The-PC-Guy :

its not as pretty as having the result in a single cell

The-PC-Guy :

but it is the only way to do this

The-PC-Guy :

without creating an infinite loop

The-PC-Guy :

see if this will work for you

Customer:

is there anyway to have the score in a single cell ?

The-PC-Guy :

not without creating an infinite loop, which is where the formula that creates the condition alters the condition its creating. Its like traveling back in time and killing your grandfather. It creates a paradox. Excel formulas are not different in certain circumstances.

The-PC-Guy :

now you can put something like

The-PC-Guy :

=max(C1:C25)

The-PC-Guy :

that will put the highest number in 1 cell

The-PC-Guy :

unfortunantelly

The-PC-Guy :

if wouldent recognize the 102

The-PC-Guy :

it would be possible with a macro

The-PC-Guy :

that is push a buton and the score is updated with whatever the last non blank row is

The-PC-Guy :

but that is not automatic

Customer:

how to do that?

The-PC-Guy :

well i would have to write a little vba macro to do that

The-PC-Guy :

do you want to see an example

Customer:

The-PC-Guy :

give me a few minutes then to come up with something

The-PC-Guy :

still working on the modification

The-PC-Guy :

be a few more minutes

Customer:

The-PC-Guy :

alrighty

The-PC-Guy :

The-PC-Guy :

now with this file you may get a sercurity warning when opening it

The-PC-Guy :

just say yes or agree to all

The-PC-Guy :

if it has a button that pops up to enable content

The-PC-Guy :

make sure you click that so the macro will run

The-PC-Guy :

otherwise it will not

The-PC-Guy :

i also automated it

The-PC-Guy :

so the score in cell e2

The-PC-Guy :

will always update based on whatever the last blank row is in B column

The-PC-Guy :

the trick is

The-PC-Guy :

to leave and B cells blank that are not being used

The-PC-Guy :

but copy the formula down as many C cells as needed

The-PC-Guy :

I changed the formula to put a blank in C cell if related B cell is also blank

Customer:

the file came as an image !

The-PC-Guy :

uh ood

The-PC-Guy :

lets try a different upload site

The-PC-Guy :

try this one

Customer:

same thing

The-PC-Guy :

very odd

The-PC-Guy :

what version of excel do you have?

Customer:

i have excel for mac

The-PC-Guy :

version?

Customer:

im not sure but is a 2008

The-PC-Guy :

ok

The-PC-Guy :

that explains it

The-PC-Guy :

VBA macros not supported on MAC 2008

The-PC-Guy :

you need to upgrade to 2011

The-PC-Guy :

or run it on windows

The-PC-Guy :

those are the options for this file

The-PC-Guy :

or

The-PC-Guy :

just use the first file I sent

The-PC-Guy :

with the cumulative score row

The-PC-Guy :

for now

Customer:

will it work on numbers ?

The-PC-Guy :

until you can get the 2011 version of excel

The-PC-Guy :

um, I dont think so

The-PC-Guy :

because it has macros

The-PC-Guy :

i wish there was another way

The-PC-Guy :

it works really nicely on my windows machine

Customer:

thank you anyway you've been a great help

The-PC-Guy :

sure you have both versions now

The-PC-Guy :

The-PC-Guy :

or use the cumulative one for now

The-PC-Guy :