Excel Calc to increment a value.

Topic

New Reply

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

Reply | Quote

Replies

Assuming you are starting at row 1:

B1 =IF(A1=””,””,1)
B2 =IF(A2=””,””,B1+1) then copy down

HTH,
Maud

Reply | Quote

Perhaps I didn’t explain what I was trying to do properly.
Column A would be blank cells.
Column B would consist of hand pre-entered 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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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?

42157-wayne1

Reply | Quote

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”.

Reply | Quote

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 right-click 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/off-screen and make a formula in B1 reference the new off-screen 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

Reply | Quote

Thanks G,

Great lesson taught and learned. I’ll go with this solution.
wayne

Reply | Quote