Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2005
    Thanked 0 Times in 0 Posts

    Too Many If statements. (microsoft XP)

    I need assistance on resolving two Problems with my spreedsheet.

    My Spreedsheet contains 14 different worksheets (Worksheets 1-12 are for each month of the year - each column representing another day in the month. Worksheet 13 is just for the import of my data from another source and worksheet 14 totals the amounts found on worksheets 1-12).

    I'm Importing data into columns D through AE and rows 4-17 and 19-27. I currently have it set up within a macro that allows the user to press a button and the next column available gets populated with the imported data.

    The first problem is that I only want the user to update one column per day. But if they keep pressing the button all the columns could be updated for that whole month. How do I write it so that the user only updates one column for that day, and yet ensure that the next column gets updated the next day.

    My Second problem is that I started out with 8 nested if statements due to there being that many cells referenced, But now my manager wants me to add 2 more references pertaining to cell R9. The first reference would be IF R9<99,1 but I also need to add it to cell R41. How can I add these two cells to ensure that all cells results will be added in cell R41 together.

    =(IF(R4<99,1,IF(R5<99,1,IF(R6<99,1,IF(R19<99,1,IF( R20<99,1,IF(R21<99,1,IF(R4=" "," ",IF(R21>=99,0)))))))))

    What I'm looking for is if cell R4 is less than 99 then cell R41 is a 1 else cell R41 is 0, it then moves to the next cell reference and checks the same thing but add the totals to cell R41. So if all five cells result in a 1 my total will be 5. But if they all should equal zero then R41 must reflect that. I hope I'm explaining this correctly.

    Any and all Assistance would be GREATLY APPRECIATED.

    Thank you. <!help=01>Help 01<!/help>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Too Many If statements. (microsoft XP)

    Problem 1: It depends on how it is setup. Does the import check or mark the date in some place? Check the date of the last entry and if it is today's date, do not import again.

    If it is an issue of "repeat data" after importing, if the 2 columns are identical delete the last one.

    If neither of those work, could you provide indications of how excel would know that it was pressed a second time in a day.

    Problem 2: How about this.
    =IF(R4=" "," ", (R4<99)+(R5<99)+(R6<99) +(R19<99)+(R20<99)+(R21<99)+etc)


  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Thanked 0 Times in 0 Posts

    Re: Too Many If statements. (microsoft XP)

    Edited by HansV to make URL clickable - see [help-19]


    Try to look at
    under the "Nested level of functions", says max is 7 - you got 8.

    Previously I've experienced similar problems and had to rethink the formula.
    It seems that the max of 7 is correct...

    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Vienna, Wien, Austria
    Thanked 0 Times in 0 Posts

    Re: Too Many If statements. (microsoft XP)

    One workaround for 8 nested IFs is to make the default for the 7th IF point to another cell. You then re-build the (old) 7th and 8th IFs in that other cell. In a data/calculation-heavy situation, this can produce problems - for example, in performance.

    <!profile=sdckapr>sdckapr<!/profile>'s solution is probably the best approach.


Posting Permissions

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