+ Reply to Thread
Results 1 to 2 of 2

How to shift blocks of formulas according to scenarios

  1. #1
    Registered User
    Join Date
    11-13-2018
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    1

    How to shift blocks of formulas according to scenarios

    I have 3 scenarios but they can happen in any order, but I do not know how to re-arrange them based on the orders.

    For example
    Scenario 1 -> 2 -> 3

    starting value
    1000

    Scenario 1 action 1 add 200
    = 1000 + 200 = 1200
    action 2 multiply by 2
    = 1200 * 2 = 2400


    Scenario 2 action 1 multiply by 2
    = 2400 * 2 = 4800
    action 2 subtract by 200
    = 4800 - 200 = 4600

    Scenario 3 action 1 divide by 2
    = 4600 / 2 = 2300
    action 2 add 400
    = 2300 + 400 = 2700


    However, it can also change order:
    Scenario 2 -> 1 -> 3

    starting value
    1000

    Scenario 2 action 1 multiply by 2
    = 1000 * 2 = 2000
    action 2 subtract 200
    = 2000 - 200 = 1800

    Scenario 1 action 1 add 200
    = 1800 + 200 = 2000
    action 2 multiply by 2
    = 2000 * 2 = 4000

    Scenario 3 action 1 divide by 2
    = 4000 / 2 = 2000
    action 2 add 400
    = 2000 + 400 = 2400


    How do I construct an excel spreadsheet which I can change the order of the scenarios and the formulas can still calculate starting from the starting value?
    Last edited by Benimarunikaido; 11-13-2018 at 10:22 AM.

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to shift blocks of formulas according to scenarios

    Hi

    Make a table with Scnenario on rows and action as columns (add, multiply, add)

    Use the following formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    where E3 is the start value, $D4:$D6 are the sequence of scnenario and $H$3:$J$5 are the constants

    See the file for clarification.
    Attached Files Attached Files

+ 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. Repeated blocks of formulas
    By Madeline g in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-16-2016, 07:42 PM
  2. day shift overtime and night shift formulas
    By Pranklin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-06-2015, 04:05 PM
  3. conditional formulas for stock picking scenarios
    By sfinns in forum Excel General
    Replies: 6
    Last Post: 11-23-2012, 12:30 AM
  4. Replies: 0
    Last Post: 09-30-2012, 01:10 PM
  5. Ctrl+Shift+Enter (CSE) array formulas in VBA using differing formulas
    By officeguy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2012, 04:31 PM
  6. String a set of formulas to merge scenarios
    By arasan25 in forum Excel General
    Replies: 2
    Last Post: 05-10-2011, 04:06 AM
  7. Replies: 2
    Last Post: 01-11-2011, 07:26 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