Hi, I am hoping someone can help me with a formula and whether it is possible or not. Basically, what I want to happen is for a figure in one cell i.e. a number in B2, B3, B4 etc to be added to 3 if there is a figure entered in another cell i.e. a 3 digit number entered into D2, D3, D4 etc. I apologise if this is not understandable but I will clarify if asked to. Thanks

Sean

I guess I could use a little more clarity. Based on what you said, I can offer 2 suggestions but I don't if they will fit your needs. You can't have a number and a formula in a cell at the same time, so if the "number" in B2 will change, you would have to put it in another cell and then put a formula in B2 that references that cell as well as D2. In other words...

In A2 you might have 10.

In B2 you could put:=IF(D2<>"",A2+3,A2)

This would put 10 in B2 if D2 was empty and 13 in B2 if D2 wasn't empty. You could change the value in A2 to get different results.

If B2 should only ever be 10 or 13, you could put this in B2:

=IF(D2<>"",13,10)

Going just a bit deeper, if you really need to check if D2 is a number or if it's 3 digits or a specific number, etc. that can also be done, but we'd need a little more info.

Hope that helps!

Alright, just for a bit of background information. I am creating the spreadsheet for my bowling team who bowls at a number of centres. What I want to do is enter each bowler's series in each centre and I want the number of games to go up by 3 every time a new figure is entered into a corresponding cell. Can I send you an email please DerbyDad03 with a copy of my spreadsheet and I'll be able to explain it a lot easier.

I've sent you an email address via Private Message. I will not be able to work on the sheet until later tonight or possibly tomorrow, but I'll see what I can do. In the meantime will this work?

Let's say the range of cells that will hold your series totals is D2:H2.

In B2 try =COUNTA(D2:H2)*3

DerbyDad03 works absolutely perfect thanks :) thank you so much for your help!!!

Another issue, the formula mentioned above counts '0' as a figure so it is giving me an incorrect amount of games. Anyway that I can stop the formula from doing this?

And one more question, the individual points system each week is on a 3 point basis so 0 and 1 point being a loss, 1.5 points being a draw and 2 or 3 points being a win. If i was to enter points won in 1 cell, what is the formula for the cell below to automatically display L, D or W?

Try this instead: =COUNTIF(D2:H2,">0")*3

Assuming the only values that will be in a cell will be BLANK, 0, 1, 1.5, 2 or 3 try this:

=IF(A4="","",IF(A4<1.5,"L",IF(A4=1.5,"D","W")))

Everything is working perfectly thank you. If there is anything else I need, which I probably will, I'll come straight to here :) Thanks

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History