Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jun 2015
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question 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

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Assuming you are starting at row 1:

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

    HTH,
    Maud

  3. #3
    New Lounger
    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 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

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 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

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, 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

  6. #6
    New Lounger
    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.

  7. #7
    New Lounger
    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".

  8. #8
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, 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 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

  9. #9
    New Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •