Wizard card game score sheet

[TWILSON29]

Good Afternoon,

Looking to make an excel formula for the card game wizard

A1 B1 C1
Bid Got Score

the rules are as follows:

if you bid correctly you get 20 points for each point you bet plus 10 for guessing right. if you bid wrong its -10 for every trick you off. example if you bid 4 and go 2 you would go down 20.

something like

= if (a1= b1, b1 x (20+10), if not a1 - b1 * (-10)

Then i need another line for the score below that is the same formula + the cell C1

Thanks in advance!!!

 

[gnrboyd]

Something like this?

Note the first row has a slightly different formula in column D because it doesn't need to count the previous entry. I made the assumption that the penalty for bidding incorrectly doesn't matter if it is over or under the actual amount. That is taken care of by the ABS (Absolute Value) part of the formula in column C. Also, you will want to make sure to not skip rows or the formulas will return an error. I could get around that but since it would be unlikely to occur, didn't bother with it. If that is a concern, let me know and I can modify it. Hope this helps.

Excel 2002
	A	B	C	D
1	Bid	Got	Score	Total
2	4	2	-20	-20
3	3	3	70	50
4	4	3	-10	40
5	1	1	30	70
6	4	3	-10	60
7	3	4	-10	50
8	2	2	50	100
9	5	4	-10	90
10	3	4	-10	80
Sheet1

Cell Formulas
Range	Formula
C2	=IF(AND(A2<>"",B2<>""),IF(A2=B2,(A2*20)+10,ABS(A2-B2)*-10),"")
C3	=IF(AND(A3<>"",B3<>""),IF(A3=B3,(A3*20)+10,ABS(A3-B3)*-10),"")
C4	=IF(AND(A4<>"",B4<>""),IF(A4=B4,(A4*20)+10,ABS(A4-B4)*-10),"")
C5	=IF(AND(A5<>"",B5<>""),IF(A5=B5,(A5*20)+10,ABS(A5-B5)*-10),"")
C6	=IF(AND(A6<>"",B6<>""),IF(A6=B6,(A6*20)+10,ABS(A6-B6)*-10),"")
C7	=IF(AND(A7<>"",B7<>""),IF(A7=B7,(A7*20)+10,ABS(A7-B7)*-10),"")
C8	=IF(AND(A8<>"",B8<>""),IF(A8=B8,(A8*20)+10,ABS(A8-B8)*-10),"")
C9	=IF(AND(A9<>"",B9<>""),IF(A9=B9,(A9*20)+10,ABS(A9-B9)*-10),"")
C10	=IF(AND(A10<>"",B10<>""),IF(A10=B10,(A10*20)+10,ABS(A10-B10)*-10),"")
D2	=IF(AND(A3<>"",B3<>""),C2,"")
D3	=IF(AND(A3<>"",B3<>""),C3+D2,"")
D4	=IF(AND(A4<>"",B4<>""),C4+D3,"")
D5	=IF(AND(A5<>"",B5<>""),C5+D4,"")
D6	=IF(AND(A6<>"",B6<>""),C6+D5,"")
D7	=IF(AND(A7<>"",B7<>""),C7+D6,"")
D8	=IF(AND(A8<>"",B8<>""),C8+D7,"")
D9	=IF(AND(A9<>"",B9<>""),C9+D8,"")
D10	=IF(AND(A10<>"",B10<>""),C10+D9,"")

 

[TWILSON29]

Gnrboyd,

Good man that's a million,didn't work exactly but close enough to get me there.
should be =IF(AND(A10<>"",B10<>""),IF(A10=B10,(A10*10)+20),ABS(A10-B10)*-1),"")

 

[gnrboyd]

I think my formula worked based upon your original post. Your new formula doesn't seem to match the criteria in the original post. Perhaps you changed the rules. Anyway, glad you have it working the way you want it to.

 

[TWILSON29]

you are correct I gave the wrong parameters. my bad, but thanks again!

 

[TWILSON29]

Ok here's another one!!!
Same Game

Under each name there is 4 cell
Tim (merged as one cell)
Bid/Got/Total/Score

I have areas for 6 players over 24 cells.
There is potential to have 20 hands in the game if you have 3 players.
15 hands with 4 people
12 hands with 5 people
10 hands with 6 people
(if you haven't figured it out here are 60 cards)
Beside each row d5-d25 I input the dealers name that appears at the top of these 6 cells.

The next formula I require scans the 6 "Player" cells that contain 4 names and 2 that still say player
and auto fills out the person’s name as the dealer.
It gets tricky because some games you can have 3 players
and others could be 6.

The formula needs to have something in it where it knows when it hits a cell still containing "Player"
it starts back at player 1

example:
4 players
# Dealer
Tim Cam Cat Bill Player
1 Tim
2 Cam
3 Cat
4 Bill
5 Tim
ect.
If someone out there can figure that one out I will be truly impressed!
Thanks in advanced. Wilson

[/URL][/IMG]

 

[TWILSON29]

Anyone?

 

[eesullivan]

I have a sheet I made years ago - it works well though we eventually figured that pen and paper was quicker.

You enter the players names and that counts for the sake of repeating the dealers, and for how many tricks is max - though we play slightly differently in that we just keep repeating max until we run out of time. Specifying the first dealer (in our case, whoever sits down last) starts the list.

All input cells are in beige.

 

[Davo50]

Anyone?

Here's a spreadsheet that works for me...

Not Found

enjoy