+ Reply to Thread
Results 1 to 6 of 6

nested if then problem

  1. #1
    Registered User
    Join Date
    09-10-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    22

    nested if then problem

    Hi -- I'm having a problem with a nested if/then formula and have attached a spreadsheet.

    I'm trying to develop a financial forecasting model with three scenarios, named 10, 50 and 90 respectively.

    The user should be able to enter into the green cell on the spreadsheet a 10, 50 or 90 to indicate the scenario they want to see.

    Then below that is a list of stores along with the scenarios each belongs to. There is a slight trick here. All the stores with scenario 10 also belong to scenario 50 and 90. All of the stores with scenario 50 also belong to 90. But, scenario 90 only includes those stores that have a 90.

    I'm trying to fill in the yellow shaded column next to each store. If the store is active then there should be a 1, otherwise a 0. I'm going to use this yellow column to multiply against budgeted sales that I have for each store (not included in spreadsheet). I think it's a nested if/then statement but I can't get it to work.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: nested if then problem

    try
    =IF($G$6=10,1,IF(AND($G$6=50,E11>=50),1,IF(AND($G$6=90,E11=90),1,0)))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: nested if then problem

    Here's another one...

    =--OR($G$6=10,AND($G$6=50,E11>=50),AND($G$6=90,E11=90))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    09-10-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    22

    Cool Re: nested if then problem

    Perfect. Thank you both very much.

  5. #5
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: nested if then problem

    @ Bobby789

    Welcome to the forum.

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: nested if then problem

    You're welcome. We appreciate the feedback!

+ 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. Nested IF, AND Problem
    By jamieray in forum Excel General
    Replies: 2
    Last Post: 09-15-2011, 07:08 AM
  2. Excel 2007 : Nested IF problem
    By Spudgunner in forum Excel General
    Replies: 6
    Last Post: 06-01-2011, 01:24 PM
  3. nested if #n/a problem
    By penfold in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-09-2008, 08:51 PM
  4. Nested if problem
    By samonly in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-14-2006, 09:40 AM
  5. nested if vba problem
    By leitek.com in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-15-2006, 05:15 PM

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