replace a value with another value off a formula. or i need help with a football pool.

[purerockfury]

so i have been toying with the idea of learning excel again just in case with my job industry changing. so instead of taking it easy i went for a big one. i have decided to help make my brother's football pool easier to manage. especially since he is married and has kids. ok so my second post is a question sorry. i have scoured the internet and this forum for the answer and i am coming up blank.

i have a lot of basics done but i am coming up blank on a couple of things. so what i am trying to do is have an area where my brother puts in let's say an * for each person's picks. when the scores are posted those people who picked the winning team the * in that cell would change to a 1. where than i can add a formula to add up everyone's wins. here is a screenshot of the set up. i have not been able to come up with a formula that works. i am thinking it might either be too complicated to do or for me to handle. which means i am done before i get started.

as you can see team a has beaten team b. what i would like to happen is where joe and tim picked the winning team their *'s will change to 1's which than i can be added together with a sum formula(which i already know how to do).
untitled-1.jpg

not sure i am giving you all enough info or not. i can post up the actual file if need be. i had this screenshot from work that was a simplified version i was practicing on.

ok so the replace function would be a way to go. but it only replaces one character in a text position to another. i can not find a version of this for numbers and formulas. what am i missing? maybe if and and commands? well back to experimenting.

[protonLeah]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.


On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.


You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.


To upload a file from your computer, click the 'Browse' button and locate the file.


To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.


Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[purerockfury]

thank you protonleah.

ok so here is the practice file i have been using at home. as you can see in the score cells team a beat team b. bob and becky picked the team that won and billy did not. now what i was hoping to do is once the score is posted up those that picked the winning team their *'s will become 1's. which than will allow me to calculate the total of wins each person has.

every formula i try or combinations of formulas and formatting does not work. hope this is a little clearer.

[FDibbins]

why not just add, eithet at the bottom or top of your table... =COUNTIF(C2:C5,"*")? that will count the "wind" for you

also, it would be advisable to change your ID name. some may find it offensive

thanks

[purerockfury]

why not just add, eithet at the bottom or top of your table... =COUNTIF(C2:C5,"*")? that will count the "wind" for you

also, it would be advisable to change your ID name. some may find it offensive

thanks

the name thing has already been addressed by another moderator when i first joined and made a newbie post. i am just awaiting the name change from the admin that i was told needed to change it for me. sorry if it offends anyone. it was a somewhat unfortunate nickname i got from my nephews when they were 2 years old 10 years ago.

ok back to my dilemna. i thought about the countif but all that does is count how many cells have *'s in them. where what i want is when a team wins a game anyone in the pool who picked that team the * put in will be changed to a 1 which than i can tally up totals with a =sum.

[dwint]

hi..

perhaps you should using this simple formula

=($B2=MAX($B$2:$B$3))*(C2="*")

copy to the right and down

please see attached file

[purerockfury]

dwint, that might be a possiblity all i would do is add an extra cell next to the pick cells. than just add those cells up for totals.

i was just hoping to have a more simple looking page. well here is my set up at the moment: