Results 1 to 9 of 9
Thread: Excel Calc to increment a value.

20150926, 13:56 #1
 Join Date
 Jun 2015
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
Excel Calc to increment a value.
Hello,
I'm am a novice user of Excel so please excuse if this question has an obvious answer.
I have a spreadsheet with a text column A and a hand entered integer column B. I am trying to increment the number in the cell of Column B when text is entered into the Column A blank cell in the same Row. I have tried everything I can think of and so far no joy.
=IF(ISBLANK(A2),B2,B2+1 This formula represents what I am trying to do but it does not increment B2 when text is entered into a blank A2 cell.
Any help would be greatly appreciated.
wayne

20150926, 14:38 #2
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,982
 Thanks
 157
 Thanked 774 Times in 706 Posts
Assuming you are starting at row 1:
B1 =IF(A1="","",1)
B2 =IF(A2="","",B1+1) then copy down
HTH,
Maud

20150926, 15:25 #3
 Join Date
 Jun 2015
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
Perhaps I didn't explain what I was trying to do properly.
Column A would be blank cells.
Column B would consist of hand preentered data that would be random integers (B2=5 for example).
When text would be hand entered into a cell on Column A (A2 for example from "" to "T" )
then the integer in Column B (B2 = 5) would be incremented by 1 (B2 now = 6)
I hope this explains my problem better.
wayne

20150926, 16:23 #4
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,420
 Thanks
 165
 Thanked 643 Times in 611 Posts
Hi Wayne
..this can be done, but you need to be very explicit with what your 'rules' are.
For example, if [A2] is blank and [B2] is 5, then, as you suggest, if you enter text into [A2] we could make [B2] increment by 1 to show a value of 6.
Now, what happens if you now delete the text entry in cell [A2]????
Should [B2] go from 6 back to 5???
Or, what if you edit (i.e. change) the text entry in [A2]?? Do you leave the current value as 6 in [B2]???
etc etc etc
zeddy

20150926, 16:55 #5
 Join Date
 Apr 2001
 Location
 Levin, ManawatuWanganui, New Zealand
 Posts
 324
 Thanks
 9
 Thanked 28 Times in 26 Posts
Hi Wayne
Are you able to introduce an extra column into the model as in attached screenshot? In this scenario the formula is in C1 and I would hide the intermediate column B.
But what happens if you subsequently change the value in example A1?
wayne1.png

20150926, 17:57 #6
 Join Date
 Jun 2015
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thank you for your reply Zeddy. You are correct, I was not being explicit enough. I was making the mistake of assuming to many details.
The perfect solution would be for the value in B2 to increment when any txt is entered in A2. AND would decrement if the txt was erased. Even if you edit the txt in A2 the results should be the same.

20150926, 18:05 #7
 Join Date
 Jun 2015
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thank you Geof,
Yes I did experiment with introducing an extra column doing the calc as you have in your screenshot and the results in column C are correct and respond as desired. However, the idea was to increment/decrement the number in Column B. I don't understand what you mean by "hiding the intermediate column B".

20150926, 19:05 #8
 Join Date
 Apr 2001
 Location
 Levin, ManawatuWanganui, New Zealand
 Posts
 324
 Thanks
 9
 Thanked 28 Times in 26 Posts
Hi Wayne
In a spreadsheet it is possible to suppress the view of rows or columns. In doing so the data and relationships are preserved.
Hide a column
Easiest way to hide a column is to rightclick on the column header and select HIDE from the context popup menu.
Unhide
Select a columns each side of the where the hidden col would be, right click and select UNHIDE.
Of course you could store the initial data in my example B1 somewhere entirely different/offscreen and make a formula in B1 reference the new offscreen location. The formula in B1 would thus be =If(a1="",?,?+1), where ? stands for the off screen cell location.
The same concerns about behaviour after editing A1 still apply.
Cheers
G

20150926, 21:40 #9
 Join Date
 Jun 2015
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thanks G,
Great lesson taught and learned. I'll go with this solution.
wayne