0

I'm going to describe my goal in steps because I think that might be the easiest way to explain it. This is what I'm trying to do:

1) Create a template that has various calculations on it. On this template, 1 specific cell is left blank. The calculations will change depending on what's in this cell (I'll refer to this as the special cell).

2) There's one final figure behind these calculations that's important. What I want to do is create a list with every possible final figure and in an adjacent cell, list the value of the special cell that gives this final figure.

The problem is Excel for Mac 2008 doesn't use macros or VBA. In my Windows version of Excel, this is just a simple function. But on Excel for Mac 2008, I'm not sure at all how to tackle this. The only solution I can think of is to create one sheet for every possible value of the special cell, with all the calculations done specifically for that value of the special cell. Then I could just link each final figure/special cell to a main page so all the information is together. However, there are roughly 400 values the special cell can take, and I really don't want to create 400 different sheets. Does anybody know how I can do this?

Also, just as a note in case this is easier to visualize what I mean, I'm basically trying to run multiple what-if scenarios and collect one specific number from each of these scenarios.

Here's an example of the processes involved. I should mention here that there are actual 2 different special cells, I wrote 1 in the original description because I'm assuming the idea would be the same to do 2:

1) The main template sheet is located on Sheet A

2) There are 10 slots for store names

3) Each store has a rate, the rate is found by applying a vlookup which looks up the special cell 1 and where the array table is located on Sheet B

4) Each store also has an index number (referred to as index)

5) Each store has a calculation which is index * special cell 2 (referred to as calc1)

6) Each store has another calculation which is rate * num1 (referred to as calc2)

7) Each store has another index number (referred to as index2)

8) Some of the index2 values have to be multiplied by calc2, the rest will stay the same (referred to as calc3)

9) A summation has to be done, summing all the calc2 values to result in sum1

10) A summation has to be done, summing all the calc3 values to result in sum2

11) The final figure is sum1 + sum2

pnuts
  • 58,317
  • 11
  • 87
  • 139
KryptKeeper
  • 125
  • 2
  • 6
  • 16
  • For #9 and #10 what do you mean by 'all the calc2 values' ? It sounds like you mean all 10 of the store values which doesnt make much sense. I created this workbook to try and visualize your above 11 rules so I could give you an accurate answer: https://dl.dropbox.com/u/19599049/120813_2b.xls Please take a look and let me know where I'm not following you. My best guess is that my idea of your 'array table' is not complex enough. – danielpiestrak Aug 13 '12 at 20:18
  • Wow, this is pretty close. You're right, the my array table is more complex, but I'm pretty sure this doesn't affect very much. The sums are actually just one number each. As in, sum1 = (e6:e15) and sum2 = g6:g15). I think you meant to use absolute reference for sum2, but there isn't one so the range of the sum is being dragged. Sum2 would just be 690 in this case. There isn't a sum1 and sum2 for each store. Therefore, the final figure would be 711 when special cell 1 is "2" and special cell 2 is "100". I hope that helps. – KryptKeeper Aug 13 '12 at 20:28
  • So you have 10 stores, 6 calculations, and you're only looking for 2 outputs? I'm just confused as to where the estimated number '400' came from in your original question. – danielpiestrak Aug 13 '12 at 20:52
  • Well, special cell 1 can take 4 different values (A-D), but special cell 2 can take 95 (1000, 2000, ..., 95000). The exact number of combinations is 380 (4 * 95). I basically have to be able to enter values for special cell 1 and special cell 2 and come up with the final figure. If I can't find a way to do it simply, I'm going to have to create a worksheet that calculates each individual final figure using every possible combination of special cell 1 and special cell 2. Does that make sense? – KryptKeeper Aug 13 '12 at 21:03

1 Answers1

1

It sounds like you could create 400 rows where each row is a what if scenario. Then next to each row you could take an input and an output, and graph accordingly.

Update

Per your description so far I've created the attached workbook with some formulas to put you in the right direction: https://dl.dropbox.com/u/19599049/120813_2c.xlsx

It calculates the sum1 and sum2 For 10 stores based on the 2 inputs. Note that I colored which cells were ending up in which final output. yellow = original sum1/sum2 blue = array formula version of sum1/sum2 green = data used in both.

I did this to point out that while this example workbook seems to follow all 11 of your rules. the input 2 doesnt appear to be included in the final outputs of my mock-up version for some reason.

Either way this should serve as a good basis to get you started. And I can modify it if you continue to include more details.

danielpiestrak
  • 5,279
  • 3
  • 30
  • 29
  • Do you mind elaborating a little bit? I'm not sure if I understand you exactly, but I should mention something I forgot to write in the main post. There are a lot of background calculations to find out the final figure, it isn't just like a formula. When you say "each row is a what if scenario", do you mean through formulas or using the actual what-if scenario feature? – KryptKeeper Aug 13 '12 at 19:28
  • I have had to do similar things that would be easy in VBA but needed to be done in a crazy formula for compatibility reasons. Can you give an example of the calculations, or the VBA function you're trying to translate into a Mac Excel '08 solution? – danielpiestrak Aug 13 '12 at 19:36
  • Okay, I added the processes to the main post. There's definitely a lot which is why I'm not sure if it's possible to put it all into one formula. – KryptKeeper Aug 13 '12 at 19:59
  • Thanks for all your help, but I'm thinking now that there probably isn't a way to do this without creating the 380 sheets. You're right, I noticed input 2 doesn't appear to be included in the calculations, but they do impact the calculations in mine. Your one-step calculation uses the table, but the entire table changes if you change either of the outputs, so you'd have to create a table for every situation, which means creating the 380 worksheets. Again, thanks for all your help! If I'm misunderstanding and there is a way to do this without creating the 380 worksheets, please let me know! – KryptKeeper Aug 14 '12 at 13:25