Function for looking up multiple variables

Question

Level 1

0 points

Function for looking up multiple variables

I've been through the F&F user guide and the numbers help guide. I'm either not seeing it or something is just going over my head. I know what I'm doing when it comes to my mac and normally I'd say I'm very capable with the standard functions/operations. I've been teaching myself the more "advanced" functions and so far it's been going well. But now I've hit a road block....

I'm trying to create a form for work, to calculate pricing for jobs.

The first table here is my reference table. the second is part of the billing sheet, which you'd be inputting information for specific jobs etc. in order to determine the price

They are on different sheets but within the same document. But I understand using formulas on different sheets, so I think for simplicity sake we can talk about them as if they are on the same sheet. (unless someone feels that's a no no). In case this is relevant, I used custom cell formats for 's/wrap' in table 2 (( ## 's )) and the prices listed in table 1 (( $##.##/m )).

	4-page	6-page	8-page	Broad	Gate	10-Page
25's	$20.00/m	$22.50/m	$23.50/m	$26.50/m	$35.00/m	$25.50/m
50's	$15.00/m	$17.50/m	$18.50/m	$21.00/m	$30.00/m	$20.50/m
100's	$12.50/m	$15.00/m	$16.00/m	$18.50/m	$28.00/m	$18.00/m
None	$10.00/m	$12.50/m	$13.50/m	$17.00/m	$25.00/m	$16.50/m

Yes/No	TRUE
Perf / Score	FALSE
Fold Type	4-page
S/wrap	25's
$/m
Sub-Total

HERE'S MY PROBLEM:

I've been trying to figure out what function to use / how to express it so that the $/m box in the second chart will come up with the correct price as listed in the top table. I can't find anything about multi-dimensional searching except that it tells me "MATCH" can't be used multi-dimensional. I've used LOOKUP for some other basic functions but I don't really understand HLOOKUP nor VLOOKUP

In the explanation for LOOKUP it mentions something about "search-where" being two-dimensional, when I try to make it two dimensional it tells me vector arguments can't be, however the formula and function guide book doesn't mention the word vector once (at least that's what the search told me).

But I THINK (think being the key word here) what I need is for the "search-for" to be two-dimensional. I'd like to be able to input a fold type and the s/wrap quantity and BAM get my price listed below.

Is this possible?

Can someone help me out here? I've been at this for hours and I'm pulling my hair out. Am I not conceptualizing something correctly or arranging my data poorly?

Any and all help would be immensely appreciated. 🙂

I hope that wasn't too long winded.

To further complicate my life, there are price discounts for larger quantity jobs. I was trying to get around this by creating price tables for the different quantity ranges (i.e. 0-7500; 75001-15,000 etc.). That is what I have the YES/NO check box for at the top of table 2. I have four tables just like table 2 on the same sheet (each table is named differently so as to be clear). But if there is a way to simplify this, that would be awesome too. I won't get my hopes up, I think I'm asking for a Hail Mary as it is. I'll provide more information if someone thinks what I am talking about is a possibility.

iMac (27-inch Mid 2011), OS X Mountain Lion

Posted on Aug 4, 2012 8:16 PM

Reply

Answer 1

Best reply

Jerrold Green1

Level 7

30,210 points

Aug 4, 2012 8:53 PM in response to gzcreezy

gz,

I suggest that you take a page from the playbook of veteran programmers and start small and simple. Then add the bells and whistles.

Get rid of every last custom format. Don't forget what you did, because you may go back to that, but you don't need that complication right now.

Then, keep your tables separate, but put them on the same sheet for easy navigation and visual reference.

If you don't find the array function that you think might make life easier, make do with an intermediate table. The extra step will make debugging easier. You can hide the extra table later. Almost anything you can do with array 2-D functions you can simulate with 1-D functions if you resort to interposing 1-D arrays.

I'm at a loss to help you beyond these generalities because I don't find all the data you say you need to reference in your example.

Jerry

Reply

Answer 2

Aug 5, 2012 6:08 AM in response to gzcreezy

Hi, GZ,

Thanks to Numbers' automatic creation of named ranges from header cell text, all you need is the INDIRECT() function. Assuming:

"4–page" as shown in your smaller table is in cell D6, and
"25's" is in cell D7

in D8, to the right of "$/m", enter

=INDIRECT(D$6&" "&D$7)
obviously, maybe, you don't enter the •

The terms in parentheses evaluate to "4–page 25's"; Numbers looks for a column headed "4–page" intersecting with a row headed "25's", and there you go. BUT NOTE THIS!

Header text that can parse as a numeric expression like 4-page with a hyphen—which is also a minus sign—will confuse things. The simple solution is to use an en-dash. Hold down the <option> key while typing a hyphen, and you'll get the slightly longer en-dash: – instead of - .

This has worked for me with your data; I hope it works for you, too.

Cheers,

David

Reply

Answer 3

Aug 5, 2012 6:11 AM in response to Good Old Monkey

Huh! Looks like the en-dash was converted to a hyphen when my reply was posted on the system. But it's there in the character set, and you type it the way I said, and it will stop Numbers from confusing hyphens with minus signs.

David

Reply

Answer 4

Aug 5, 2012 8:53 AM in response to Good Old Monkey

You may have found a good use for Header Names as References. I find it a nuisance and a source of confusion.

Glad your problem is solved.

Jerry

Reply

Answer 5

gzcreezy Author

Level 1

0 points

Aug 5, 2012 9:01 AM in response to Jerrold Green1

Thank you for the "back to basics" suggestion. Removing the custom formats made it easier to see things and also showed my how specific and exact Numbers needs you to be. That seems obvious now that I say it out loud, I suppose I just overlooked that concept.

Reply

Answer 6

gzcreezy Author

Level 1

0 points

Aug 5, 2012 9:05 AM in response to Good Old Monkey

RE: Good Old Monkey.

THANK YOU THANK YOU THANK YOU!

It took me a little while to finally grasp what you were saying. But I got it, I think.

I'm in the dark as to how INDIRECT chooses a table to search. Does it search the entire document/sheet? I am going to work under the assumption that I need to be more careful about how I phrase my Header names.

Reply

Answer 7

gzcreezy Author

Level 1

0 points

Aug 5, 2012 9:08 AM in response to Jerrold Green1

RE: Jerrold

I thought about what you said in reference to intermediary tables. I've figured out how I can add the third variable of quantity! I had to add a couple rows to my tables, and I've added a table to determine which pricing level to use. I'll post up an example to show you what I'm talking about.

Reply

Answer 8

Aug 5, 2012 9:38 AM in response to gzcreezy

gz,

INDIRECT is a way to create a cell reference from a string, either a literal string or the result of a text function. INDIRECT is not a search function. For example, if you enter the simple expression: =C2, the compiler knows to convert C2 to an address token. If you write: ="C2", your cell will simply display C2, not the contents of cell C2.

However, if you write: =INDIRECT("C2"), the cell you enter that expession in will display the same thing that is being displayed in cell C2 because INDIRECT converts the string "C2" to the address C2.

Regards,

Jerry

Reply

Answer 9

gzcreezy Author

Level 1

0 points

Aug 5, 2012 9:40 AM in response to gzcreezy

I wanted to thank you two again, you were both very helpful with your responses. I wanted to post this up again, to show you how you've helped, and maybe help someone else that needs help like this later.

*******************************************************

Fold

Yes/No	TRUE
Folding Price Level	Level 1
Perf Score	FALSE
Fold Type	Single25
Price per M	20.00
Sub-Total

Note:

"yes/no" is a check box (well the check box is to the right of it, idicated by TRUE) which I will use later to instruct Numbers when to add the amount from this table to the total price of the job

(you can ignore the "perf score" line. it currectly has no influence/purpose)

Fold type: I used CONCATENATE to join the terms from the "Job Information" table shown below, which follows protocol and registers the correct headings in "Folding Price"

RE: GOM -- the INDIRECT function works great. I've realized that it does search the entire document for headings that match the parameters. I copied these tables onto another sheet so I could mess around with them without destroying what I've done, and I couldn't get the INDIRECT function to work. But as soon as I deleted the sheet with the copied tables, it corrected itself.

I can't thank you enough. Your suggestion just took days off the workload for this project. I'm working with a lot of different elements here and now you've given me a way to simplify things for myself and the user. Thanks again!!

*******************************************************

Folding Price

	Single25	Single50	Single100	SingleNone	6pg25	6pg50	6pg100	6pgNone	8pg25	8pg50	8pg100	8pgNone	10pg25	10pg50	10pg100	10pgNone
Level 1	20.00	15.00	12.50	10.00	22.50	17.50	15.00	12.50
Level 2	17.50	12.50	10.00	7.50	20.00	15.00	12.50	10.00
Level 3
Level 4

Note: I know this table looks like one giant string of information at the header row. But it doesn't look that way on my sheet. I just don't have time to be neurotic about this post! Haha 😉

Note: different pricing levels are necessary for the project because we offer discounts on larger quantity jobs. In the table above "Single25" refers to creating a single fold and wrapping the job in stacks of 25. Both the fold type and the wrapping effect the price of the job.

*******************************************************

Folding Price Levels

	Quantity
Level 1	0-7499
Level 2	7500-14999
Level 3	15000-29999
Level 4	30000+

RE Jerrold:

This is what I took from your suggestion. I made a separate table to classify the different price levels by the quantity of the job. Which will now correspond to the Header Column of "folding price"

-I had previously planned on having 4 tables similar to the "Fold" table listed about, but each was to be titled by the different price levels and the user would have had to choose which table to use (I am positive that they would have done it wrong 99% of the time) and each of these tables was going to need a corresponding "folding Price" table. So we're taling about eliminating 6 tables right there, and this concept will be transferable to the other parts of the jobs, removing comprable amounts of tables. You've saved hours of "programming" and data entry.

-removing the custom formats was also clutch. It really simplified things for me.

Thanks again!!!

*******************************************************

Job Information

Job #
Description
Form/Item #
Quantity	5000
Sheet Size	11x25.5”
Stock	Standard
Press
Fold Type	Single
S/wrap	25
Pieces Chip

*******************************************************

YOU GUYS ARE ROCK STARS!!!

As far as the user experience of the form. The user will simply fill in the details in the "job information" table, and the form will (should) auto-populate everything I need. After that they just need to check the box in the "Fold" table and I'm golden.

JE: What you said about header names being a nuisance is definitely true, (at least I'm finding this out now). I plan to use "pop-up" menus to prevent users from entering improper values.

I have to make this form as simple as possible for the user. It's turning out to be not so simple for myself, but "nothing is ever easy"

Reply

Answer 10

gzcreezy Author

Level 1

0 points

Aug 5, 2012 9:42 AM in response to Jerrold Green1

Thanks. That was helpful. I was having trouble with the function working properly because I had multiple copies of the tables within the document. So if I'm understanding this all right, I was essentially telling Numbers to reference the "same cell" on different tables.

Reply

Answer 11

gzcreezy Author

Level 1

0 points

Aug 5, 2012 9:44 AM in response to gzcreezy

Maybe I just don't understand the linguistic scheme of the F&F guide. But I really don't find it helpful at all. I don't feel it breaks down the concepts simply enough. But I think I need to study it more instead of skimming and looking for information I think I need. I should go through it more thoroughly.

Reply

Answer 12

Aug 5, 2012 9:49 AM in response to gzcreezy

GZ,

INDIRECT() uses the table it's in, or if you want to reference another table, put it in like this:

=INDIRECT("that_other_table::"&$C2&" "&$D2) or something similar. OR you could have the name of the other table plus its two colons in cell B2, and then you'd say =INDIRECT($B2&" "&$C2&" "&$D2) Don't forget

two colons :: after the table name
&" "& between references if you want to end up with a result Numbers can interpret as two ranges.

Cheers,

David

Reply

Answer 13

Aug 5, 2012 10:05 AM in response to gzcreezy

gzcreezy wrote:

Maybe I just don't understand the linguistic scheme of the F&F guide. But I really don't find it helpful at all. I don't feel it breaks down the concepts simply enough. But I think I need to study it more instead of skimming and looking for information I think I need. I should go through it more thoroughly.

I think you will find that the definitions in the Formulas and Functions user guide were essentially taken from Excel guides, where they were essentially taken from spreadsheet programs upon which Excel was written. It's a legacy thing. The best way to understand the workings of the functions is to study the examples.

Edit: Regarding the multiple tables with the same name. It's good to try to avoid that. Yes, you can make them unambiguous by placing them in different Sheets, but that just complicates the addressing. Go ahead and put your unambiguously named tables in whatever sheets you want, but since they are not repeated names, you can leave off the sheet name when addressing the tables.

Jerry

Reply

Answer 14

Aug 5, 2012 10:36 AM in response to Jerrold Green1

Hi again, Jerry,

If not with header names, which confuse me all the time, how do you create a three-variable lookup table? Let's say I want to find the price of Jumbo Frozen Shrimp in a situation where my seafood items come in Large Jumbo and Colossal sizes, Fresh or Frozen, and include Shrimp, Scallops, Scrod and Swordfish?

Once I've remembered how to organize multiple headers, I find the formula = Jumbo Frozen Shrimp really easy to understand. It was actually because Google Docs lookup functions don't work right, and because I found that form of reference easy to understand once I had the right hierarchy of headers, that I'm doing most of my current model in Numbers. But if there's an easier way, I'd love to know it.

Cheers,

David

Reply

Answer 15

Aug 5, 2012 10:50 AM in response to Good Old Monkey

David,

Please post a small example of your table where the price data is stored.

Jerry

Reply