simplifying a very long formula!

Question

Level 2

380 points

simplifying a very long formula!

Hi

I'm trying to simplify a formula. I don't mind the way it currently is, it's easy to see what's happening and to amend if necessary, but it just takes forever to edit or amend (despite having a good Mac, I'm forever seeing the spinning beach ball!).

I think because of it's length, it's soooo slow to edit. There's at least 15 groups of formula (below is just a snap shot of 3):

I'd appreciate any ideas to either make it easier to edit or to simplify, thanks.

Ric

macOS Sierra (10.12.4)

Posted on Jul 1, 2017 5:39 AM

Reply

Answer 1

Yellowbox

Level 7

22,307 points

Jul 1, 2017 7:59 AM in response to ric frankland

Hi Ric,

it's easy to see what's happening and to amend if necessary

Um.... no.

I am having trouble understanding your overall aim. Please reply with what you want to do, not "how do I make this formula work".

Numbers is built around several small tables, each with a purpose.

A screen shot of small, relevant part(s) of your source table(s) and summary table will perhaps lead to a solution. Please select the table(s) to show the Row numbers and Column letters

Hint: in these forums, it helps other users if you turn off (untick) this in Numbers Preferences > General

so that we can see the cell references.

I look forward to helping you.

Regards,

Ian.

Reply

Answer 2

ric frankland Author

Level 2

380 points

Jul 2, 2017 2:28 AM in response to ric frankland

I've just created a new sheet based on the same formula, but a lot simpler, to hopefully explain and test this...

(is it possible to upload this file??)

as said, there will be at least 15 maybe as many as 30 "codes" and then more fixed costs and options... which I think is why it's so slow to edit/amend.

Reply

Answer 3

Yellowbox

Level 7

22,307 points

Jul 2, 2017 4:43 AM in response to ric frankland

Hi Ric,

I am still having trouble seeing your aim, but here is a thought.

if a quantity in column C is greater than 0, and then add up all the numbers that correspond to the codes above.

You can simplify the formula by not testing for 0. For example, I think this line (and other similar lines) is redundant

Please correct me if I am wrong.

SUMIF interprets zero (or a blank cell) as 0. Here is a trivial example:

Formula in Footer Cell C9 =SUMIF(B,TRUE,C)

Regards,

Ian.

Reply

Answer 4

Yellowbox

Level 7

22,307 points

Jul 5, 2017 6:43 AM in response to ric frankland

Hi Ric,

Perhaps this multi-dimensional problem (calculating costs for this huge number of combinations) is beyond the scope of Numbers or any spreadsheet.

One way you could simplify the formula is by dealing with one set of options at a time.

Here is a table with options for Codes, Windows, Doors, Cladding and can be extended to the right to include others (up to 20 options you say).

The cells in Row 2 are Pop-Up Menus

and so on... Giving just one combination of options to cost at a time, rather than 15 different building designs each with 20 options plus fixed costs all at once!

That could lead to a quote for a customer after you have determined their requirements.

Good luck and Happy Numbering!

Ian.

Reply

Answer 5

SGIII

Level 8

38,006 points

Jul 6, 2017 3:44 AM in response to ric frankland

Hi Ric,

Instead of a long, repeating formula you might consider a formula that looks up relevant data from a table and sums it. In this example based on your example I enter the Design Name in B1 of Table 1 and select the options in the 'Include' column. The formula in B2 then automatically tallies the cost.

The formula in B2 is:

SUMIF(Cost Data::B,TRUE,INDEX(Cost Data::A:F,0,MATCH(B$1,Cost Data::$1:$1,0)))

The MATCH simply looks up the name if finds in B1 in row 1 of the 'Cost Data' table and returns the column number. It feeds that column number to INDEX which retrieves the values in that column and feeds them to SUMIF, which sums all the numbers where the checkbox in column B is checked (TRUE).

To see how this approach works it may help to set up an example exactly as shown, then when you have it working adapt it to your needs.

SG

Reply

Answer 6

Jul 1, 2017 6:32 PM in response to ric frankland

Like Ian, I suggest showing the context and describing what the formula is doing. Then let us propose other solutions. without that information I think you can always simplify by making the data more convenient to sum. Organize summaries to facilitate this

Reply

Answer 7

ric frankland Author

Level 2

380 points

Jul 2, 2017 1:34 AM in response to ric frankland

Thanks for the responses Ian and Wayne. I thought/think adding more screenshots and explaining the source etc might only make it even more confusing. They're also very large tables!

I thought having the cell references in the formula would be sufficient to see what's happening, apologies if this isn't clear.

Basically the very first reference (in C2) will have different codes ("01-05", "01-06", "01-07"... and so on). Depending on which has been selected this formula will check to see if checkboxes have been ticked in column C, or if a quantity in column C is greater than 0, and then add up all the numbers that correspond to the codes above.

So column Z are the quantities relating to code "01-07"; column AD are the quantities relating to "01-08"; and column AH are the quantities relating to "01-09-1".

I though there must be a simpler formula so that instead of repeating the same full formula for every code, the formula will just select the amounts 4 columns over for each subsequent code....?

I also just wanted to check whether I was right about the size of this formula (having at least 15 different code references) was the reason it was so slow, or whether there was potentially something else wrong?

Reply

Answer 8

ric frankland Author

Level 2

380 points

Jul 3, 2017 12:26 AM in response to Yellowbox

Hi Ian

The current formula works fine, but because of the large number of lines (almost 30 "codes" and around 10 "options" for each code), it takes a very long time to edit or add to it. My aim is to find a much shorter and efficient way to do the same thing, so it's quicker to manage.

I thought this would be possible because of the amount of repetition in the formula. Because the option checkboxes and quantities are all in the same column, and the results for each code appear 4 columns over to the right of the previous code, I somehow need a formula that appears once, but finds/provides the results for each code in a specific column... but I don't know how to do this. I'm finding this hard to explain (!!) but hope this is clearer?!

Here is a link to my (much simplified) test file... hopefully this will make it clearer: Dropbox - test-sheet.numbers

Thanks for the suggestion above about...

not testing for 0

but if this column contains a number (greater than 0), then I still need it to be included in the result.

Cheers.

Reply

Answer 9

SGIII

Level 8

38,006 points

Jul 3, 2017 9:21 PM in response to ric frankland

Hi ric,

I think when Ian is asking about your aim he's asking for more perspective on what you are trying to do with Numbers (not that your aim is to simplify your particular formula).

A long formula like that, even if it works, and lots of blank cells within tables is almost always a good indicator that the approach is not efficient. Very rarely should you need long formulas in Numbers. Have a look at the templates at File > New in your menu for examples of efficient Numbers document design.

It's hard to suggest a better approach for your document without understanding the problem.

SG

Reply

Answer 10

ric frankland Author

Level 2

380 points

Jul 5, 2017 1:13 AM in response to SGIII

Hi SG

Ok, but I'm not sure of the relevance of understanding what the whole document does... but anyway, I'm trying to accurately work out the overall costs of what is around 15 different building designs and each has a number of options. The number of buildings might end up to be around 30. Although my file has quite a few sheets (so one for all the individual component parts of the building, another for doors and windows, another for cladding etc), so long as I keep the overall size of each sheet and each formula relatively small, I'm totally amazed at the power of Numbers - it's a fantastic tool.

This formula is one of those which is getting quite big (and therefore slow to update/amend) because it's calculating the total cost of each design, and each design has maybe 20 or so options plus some fixed costs.

But, because of the amount of repetition in the formula, I thought there must be a simpler way of organising it, so that with each different design (the "code"), it just calculates the total from the different column of figures...

Hoping again this makes sense and helps?

Ric

Reply

Answer 11

ric frankland Author

Level 2

380 points

Jul 6, 2017 3:52 AM in response to Yellowbox

Hi Ian

Just out of interest, if I am hitting the limits of what Number is able to do, and as I've said, so far it works really well albeit there are times when it gets a little slow with big formulas... what other software or system would you recommend? Some for of database or Filemaker?

Ric

Reply

Answer 12

Yellowbox

Level 7

22,307 points

Jul 6, 2017 8:26 AM in response to ric frankland

Hi Ric,

Perhaps I misunderstood your overall aim. I thought that you were trying to calculate every combination at once, and that would certainly challenge Numbers or any other Spreadsheet app!

Give SG's approach a try. I find it to be impressive, elegant and simple.

Regards,

Ian.

Reply

Answer 13

ric frankland Author

Level 2

380 points

Jul 7, 2017 12:03 AM in response to SGIII

Cheers SG!

I don't know if you saw the link above to a test file I uploaded to Dropbox (Dropbox - test-sheet.numbers) - I've updated this using your formula below my original one. Because it doesn't take into account some figures in the same column as the checkboxes, I've tried adding an OR function but this doesn't work... could you please take a look and let me know why?

The other thing, which might be a bigger problem is that the formula selects the whole table, but in my actual spreadsheet, I have some figures midway in each column that shouldn't be included in the total, as these are added up separately to create subtotals (that are selected).

I've tried breaking up the collection area the MATCH function is checking, but this doesn't work - it tells me the range arguments aren't the same size.

This is tricky!

:-/

Ric

Reply

Answer 14

SGIII

Level 8

38,006 points

Jul 7, 2017 7:36 AM in response to ric frankland

Hi Ric,

ric frankland wrote:

Cheers SG!

I have some figures midway in each column that shouldn't be included in the total, as these are added up separately to create subtotals (that are selected).

Hi Ric,

Generally not a good idea! Simplify things so that each column includes one type of data. No "different" things midway. That will complicate life.

SG

Reply

Answer 15

ric frankland Author

Level 2

380 points

Jul 7, 2017 8:45 AM in response to SGIII

hmmm... not sure I can really do that, not now... the sheet's all set up and works really well as it is.

this particular formula is more of a check, to ensure all the costs tally with those on another sheet.

Reply