Complex table calculations

[nickmellor]

Hi,

I have a spreadsheet table of employees using two columns, "days off due to back pain" and "salary",which are both used as arguments for about 10 calculated columns. Each of these columns uses a different formula,to calculate "break-even subsidy", "return on investment" for each of 3 scenarios.

Each of ROI, break-even subsidy for each scenario is a complex calculation in itself. In Openoffice Calc I effectively use a separate sheet and feed it "days off due to back pain" and "salary" as arguments to recalculate for each cell.

The table looks like this:

Employee 1,Annual Salary,Days off with back pain,ROI 6 AT lessons, Break-even subsidy 6 AT lessons,ROI 24 AT lessons,Break-even subsidy 24 AT lessons,...
Employee 2,...
...
Employee n,...

As soon as an employee's details are put into Calc, it computes the figures for that employee.

How would I do this in Excel?

Thanks for any help. I can forward the worksheet to anyone who wants to see it, but obviously it's only working in Openoffice Calc so far.

Best wishes,

Nick

 

[milesUK]

Nick, what happens when you open the OOCalc file in Excel? Excel 2007 seems OK with that at least but earlier versions may require that you Save the Calc file in an Excel format from OpenOfficeCalc. Do any of the formulas work at all?

 

[nickmellor]

Hi Miles,

Thanks for your reply. To answer your question, Excel translates the formula into a number.

My understanding from reading other threads at the Openoffice community forum is that Excel is comfortable if it's converting one-way and two-way sensitivity tables, but as soon as you try something more complex or unusual, it won't convert.

So I'm resigned to rewriting the spreadsheet for Excel. My problem is that I don't know how Excel would achieve the same effect: feeding a couple of cells in a sheet with values, and then reading off the value of another cell after the substitution.

In Openoffice I use the multiple.operations() function. Simplified, here's one of my formulae:

=MULTIPLE.OPERATIONS($AT6LessonsCalcs.$B$47;$Main.$B$15;$B6;$Main.$B$18;$C6)

which means: value of at6LessonsCalcs.b47 when I put b6 in Main.b15 and c6 in Main.b18

What I'm wanting to do is feed in a name, salary and days of back pain, and get back a series of reasonably sophisticated analyses of the costs of this employee's back pain to the organisation, offsetting the cost of various possible interventions for this employee. In other words, how to improve an employee's spinal health and save the organisation money as a result. The interventions are all based on the Alexander Technique.

Best wishes,

Nick
www.back-pain-self-help.com

 

[nickmellor]

Hi Miles,

I'm using Excel 2007.

Nick

 

[milesUK]

Nick, i did a quick search for "MULTIPLE.OPERATIONS" but came up blank. I'll have a guess at how to convert

$AT6LessonsCalcs.$B$47;$Main.$B$15;$B6;$Main.$B$18;$C6

IF $Main.$B$15=$B6 AND $Main.$B$18=$C6 THEN
   RETURN value from $AT6LessonsCalcs.$B$47
   ELSE RETURN a blank

in XL this would be written as

=IF(AND($Main.$B$15=$B6, $Main.$B$18=$C6), $AT6LessonsCalcs.$B$47,"")

However I suspect that MULTIPLE.OPERATIONS is used in a an array context as the above seems pointless to me and that's where I falter.

I'll admit defeat here and hope someone else can help.

apologies,

 

[nickmellor]

Hi Miles,

Thanks for your efforts to help me. I think you might have misunderstood-- your translation of what I'm asking seems to be comparing cell contents rather than substituting them.

Just a quick reminder: within a calculation, I want to be able to plant one or two values into a couple of cells, then read a result cell which is dependent in some arbitrarily complex way on those two cells I've substituted. And I want this process to take place as a function call.

One application of this is for pivot tables: take the row and column headings, substitute their values in a calculation, then read the result for the current pivot table cell. In this case, yes, the facility happens to be being used for computing an array of either one or two dimensions. This I can easily do in Excel using my existing knowledge.

But what if I don't want to use the same formula for every cell in a table? What if it isn't appropriate to put one single formula in the top-left, as in a pivot table? Mine is such a case, because each column is calculated using a different algorithm.

As far as I know (and I don't know much) Excel doesn't have an elegant solution for my table.

Could scenarios help? I can't really see how: I might have a list of several thousand employees.

Could Excel 2010 help? That would be worth knowing.

Any help gratefully received.

Best wishes,

Nick

 

[milesUK]

Nick, you are correct i'm not quite on your wavelength.

You mention "function call" - are you aware that you can create your own User Defined Functions with VBA and use them like other formulas?

If the placement of the formulas obey a set of rules, could you use VBA to place formulas into appropriate cells?

Clutching at straws perhaps.