Dell / Mm061...
February 24, 2009 at 08:01:59
Specs: Microsoft Windows XP Professional, 1.729 GHz / 1014 MB
 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.ThanksSean

#1
February 24, 2009 at 09:54:43
 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!

Report •

#2
February 25, 2009 at 02:33:28
 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.

Report •

#3
February 25, 2009 at 05:49:03
 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

Report •

Related Solutions

#4
February 25, 2009 at 21:09:55
 DerbyDad03 works absolutely perfect thanks :) thank you so much for your help!!!

Report •

#5
February 26, 2009 at 07:05:28
 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?

Report •

#6
February 26, 2009 at 09:17:23
 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?

Report •

#7
February 26, 2009 at 10:20:19
 Try this instead:=COUNTIF(D2:H2,">0")*3Assuming 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")))

Report •

#8
February 26, 2009 at 16:54:31
 Everything is working perfectly thank you. If there is anything else I need, which I probably will, I'll come straight to here :)Thanks

Report •