+ Reply to Thread
Results 1 to 2 of 2

Combining IF and OFFSET Functions

  1. #1
    Registered User
    Join Date
    02-28-2017
    Location
    Pennsylvania, USA
    MS-Off Ver
    2010
    Posts
    7

    Combining IF and OFFSET Functions

    I have provided a simplified example below, which I will reference in my explanation.

    What I am attempting to do is have Column B return the first value in Column D (in this case 10) and repeat this until it encounters the initial Animal again (cat). Once the next instance of "cat" is encountered, Column B will begin to return the next value in Column D (in this case 25).

    So the following formula is what I'm attempting to use:

    =IF(A2="cat",OFFSET(D2,0,0),0)

    I know what I need it to do...I need the bolded 0 to increase by 1 every time "cat" is encountered in Column A. And I also need the FALSE condition to return the correct value that corresponds to that instance of "cat".....I just don't know how to do that in Excel speak.

    Thanks in advance!


    example.PNG

  2. #2
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: Combining IF and OFFSET Functions

    Have a look at using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Dragging that down will give you a running total of how many "cats" there have been so far.

    You can also use INDEX() to replace your offset (imo a better choice)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    where x is your running "cats" total.

    For the false condition you can just use the value from the cell above.
    Design everything to be as simple as possible, but no simpler.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Combining IF and OFFSET
    By hurrell8510 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-14-2016, 09:19 AM
  2. Need help in combining IF into OffSET
    By shuriyan0924 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-27-2015, 04:07 AM
  3. [SOLVED] Combining Offset or Index and Sum functions
    By tonylyx in forum Excel General
    Replies: 3
    Last Post: 01-26-2015, 12:33 AM
  4. [SOLVED] Combining IF, OFFSET and INDIRECT functions.
    By dowell89 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-30-2013, 05:50 AM
  5. [SOLVED] Combining OFFSET, Indirect and IF functions
    By dowell89 in forum Excel General
    Replies: 3
    Last Post: 10-25-2013, 06:20 AM
  6. Combining multiple functions>lookup/sum functions
    By mush106 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-22-2013, 07:47 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1