Previous 1 2 Next 21 Replies Latest reply: Aug 6, 2012 7:30 PM by Jerrold Green1
gzcreezy Level 1 Level 1 (0 points)

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
  • Jerrold Green1 Level 7 Level 7 (29,935 points)

    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

  • Good Old Monkey Level 1 Level 1 (15 points)

    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

  • Good Old Monkey Level 1 Level 1 (15 points)

    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

  • Jerrold Green1 Level 7 Level 7 (29,935 points)

    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

  • gzcreezy Level 1 Level 1 (0 points)

    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.

  • gzcreezy Level 1 Level 1 (0 points)

    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.

  • gzcreezy Level 1 Level 1 (0 points)

    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.

  • Jerrold Green1 Level 7 Level 7 (29,935 points)

    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

  • gzcreezy Level 1 Level 1 (0 points)

    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"

  • gzcreezy Level 1 Level 1 (0 points)

    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.

  • gzcreezy Level 1 Level 1 (0 points)

    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.

  • Good Old Monkey Level 1 Level 1 (15 points)

    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

  • Jerrold Green1 Level 7 Level 7 (29,935 points)

    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

  • Good Old Monkey Level 1 Level 1 (15 points)

    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

Previous 1 2 Next