How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1402
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

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

Resolved Question:

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
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 1 year ago.

The-PC-Guy :

Thanks for using JustAnswer. My name isXXXXX will do whatever I can to answer your question

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 :

PLEASE RATE MY SERVICE

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 :

sorry about that

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 :

PLEASE RATE MY SERVICE

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:

if possible please

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:

no problem take your time

The-PC-Guy :

alrighty

The-PC-Guy :

uploading now

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 :

so if you upgrade you can switch to the newer one

The-PC-Guy :

or use the cumulative one for now

The-PC-Guy :

please rate my service

The-PC-Guy :

thanks

Customer:

thank you have a great day

The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1402
Experience: 20 years experience providing remote computer support
The-PC-Guy and 3 other Microsoft Office Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions

Chat Now With A Microsoft Office Technician
The-PC-Guy
The-PC-Guy
327 Satisfied Customers
20 years experience providing remote computer support