OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

I'm Stumped - Some sort of lookup/spreadsheet issue?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Lek
Power User
Power User


Joined: 20 Oct 2006
Posts: 73

PostPosted: Tue Feb 03, 2009 4:05 am    Post subject: I'm Stumped - Some sort of lookup/spreadsheet issue? Reply with quote

Been a while...winter again and am again working on some stuff while slow.

Doing a simple estimator for ornamental fence.
I have a master sheet of pricing (yes I know..databases...just do not know enough and while not perfect solution, should work, no?).

Columns
Item B C D Cost

Have about 675 Items
Using 1000 for range in case i add more in the future.

Items are typical of the following ranging through all available sizes per item:
Appalachian – 48” – Res.
Appalachian – 60” – Res.
Manhattan – 36” – Res.
Manhattan – 42” – Res.
2” x 58” x .062 – Line Post
2” x 58” x .062 – Blank Post
2” x 70” x .062 – Line Post
36” H x 72” W – Walk Gate – Ind.
42” H x 42” W – Walk Gate – Ind.
42” H x 48” W – Walk Gate – Ind.
14oz. Can of Bronze Touch up Paint
One Time Set Up Fee – Standard
Res. Ring Installation Charge
Double Punch Section

OK...now I have another file that estimates.
One Page, takes the results of drop down lists on other sheets used to figure Qty and Parts.
All Items on this page refer to another cell in the spreadsheet for its results.

Been using a basic lookup function.
All seemed to be working well until I went used it fro Double Punch Sections.
But...this returns the price of the 6th Item...not item 671, which it is.

Did some looking here and saw the post with the Match_0_1 attachment.
Looked at that...figured maybe I just try the MATCH/INDEX thing.

MATCH returns the 6th Item...which btw is
A ----------------------------------B----------C---------D----------E
Appalachian – 72” – Res. $99.75 25.00% $24.94 $74.81

Formula I am using on Match:
=MATCH(D13;'file:///C:/FENCE DATA/Sentinel Fence Pricing - Master.ods'#$Sheet1.A5:A1000;'file:///C:/FENCE DATA/Sentinel Fence Pricing - Master.ods'#$Sheet1.E5:E1000)

Formula I used for Lookup:
=LOOKUP(D13;'file:///C:/FENCE DATA/Sentinel Fence Pricing - Master.ods'#$Sheet1.A5:A1000;'file:///C:/FENCE DATA/Sentinel Fence Pricing - Master.ods'#$Sheet1.E5:E1000)

D13 = Double Punch Section


Everything listed in items on the pricing spreadsheet is unique....


Have no idea why this is not working on this item as it has worked on all posts and sections and any other area add-ons I have done so far.

Appreciate any help.

Bob
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 03, 2009 4:16 am    Post subject: Reply with quote

LOOKUP, MATCH, VLOOKUP and HLOOKUP work like they use to work in most other spreadsheet applications of the past 2 decades.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Lek
Power User
Power User


Joined: 20 Oct 2006
Posts: 73

PostPosted: Tue Feb 03, 2009 4:23 am    Post subject: Reply with quote

Sorry,

OK....I now realize why the MATCH was not working....duh

I'll take another look at the LOOKUP and see where i was slightly non-intelligent there.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 03, 2009 4:42 am    Post subject: Reply with quote

http://www.oooforum.org/forum/viewtopic.phtml?t=79368

x is the search value
v1 is the vector where to match search value x
v2 is the vector to return some value from

INDEX(v2 ; MATCH(x ; v1)) is an alternative for LOOKUP(x ; v1 ; v2)
horizontally from row #1: INDEX(v2 ; 1 ; MATCH(x ; v1))

There is no alternative for unordered "database mode"
INDEX(v2 ; MATCH(x ; v1 ; 0))
since LOOKUP works horizontally and vertically in ordered mode only.

VLOOKUP is a convenient alternative if your search vector happens to be the first column of an adjacent range:
VLOOKUP(x ; range ; col_number)
VLOOKUP(x ; range ; col_number ; 1) [1 is default if missing]
unordered "database mode"
VLOOKUP(x ; range ; col_number ; 0)

HLOOKUP is a convenient alternative if your search vector happens to be the first row of an adjacent range:
HLOOKUP(x ; range ; row_number)
HLOOKUP(x ; range ; row_number ; 1) [1 is default if missing]
unordered "database mode"
HLOOKUP(x ; range ; row_number ; 0)

Additionally, MATCH knows a third mode (-1) for descending ordered mode:
INDEX(v2; MATCH(x ; v2 ; -1))

Tools>Options...Calc>Calculations influence how text is matched:
Using regex-patterns (this is a trap if unwanted) or plain text as is.
Case sensitively or not
Match whole strings or partially "b" in "abc"

Matching numbers can be tricky when matching calculated figures. This is a very common spreadsheet problem due to the way how spreadsheets calculate numbers (floating point rounding errors).

The combination of INDEX and MATCH can do everything [V/H]LOOKUP can do plus descending ordered mode.

This is how MATCH and [V/H]LOOKUP are supposed to work by default since they are spreadsheet functions rather than database functions:

v1 v2
0 Low
1 Mid
2 High

LOOKUP(-1;v1) => #NA
LOOKUP(0;v1) => 0
LOOKUP(0.5;v1) => 0
LOOKUP(1;v1) => 1
LOOKUP(1.5;v1) => 1
LOOKUP(2;v1) => 2
LOOKUP(999;v1) => 2
LOOKUP(x;v1;v2) => the respective value from v2

LOOKUP and all other functions match at the position where the last value is <=X in default ordered mode.
LOOKUP&Co. give unreliable results in default ordered mode if v1 is not sorted ascending.
In default ordered mode LOOKUP&Co. return #NA if and only if the search value is smaller than the minimum.

MATCH, VLOOKUP and HLOOKUP accept 0 as additional last argument for unordered "database mode". Of course this will not match 1.5 between 1 and 2 anymore.

EDIT (final edit, I hope) 2009-02-04
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org


Last edited by Villeroy on Wed Feb 04, 2009 8:05 am; edited 4 times in total
Back to top
View user's profile Send private message
Lek
Power User
Power User


Joined: 20 Oct 2006
Posts: 73

PostPosted: Tue Feb 03, 2009 6:00 am    Post subject: Reply with quote

Sorry...posted the below prior to me just realizing you posted again.


I found that link yesterday and was working on/off it.
Guess, just not fully understanding the formula layouts.
Only spend what amounts to no more than a week or so a year really doing something..always re-learning what little I know.

Any event.
Got it to work using individual MATCH INDEX....

Got it to work with VLOOKUP, though seems that my formula is just a tad different as I needed a zero sort to get the proper result)
=VLOOKUP(D13;'file:///C:/FF14ENCE DATA/Sentinel Fence Pricing - Master.ods'#$Sheet1.$A$1:$E$1000;5;0)
The blue being data range
Column
Sort
(always was under the impression with VLOOKUP that the two columns had to be side by side, bot sure where I got that...which is why I did not even try it and got stuck on LOOKUP)

As far as LOOKUP...I still can not figure that one out relative to what I am doing....tried and tried using the formula in the referenced threads spreadsheet.......just can't seem to grasp that one and make it work....When i get out of Index...all i get is the column header as a result, or REF# if it is any number but a 5.
=LOOKUP(D13;'file:///C:/FENCE DATA/Sentinel Fence Pricing - Master.ods'#$Sheet1.A1:A1000;INDEX('file:///C:/FENCE DATA/Sentinel Fence Pricing - Master.ods'#$Sheet1.A1:E1000;0;5))


In any event...THANKS!....as long as one method will work and do the job....that is fantastic.

...do wish I could retain more of this from winter to winter.


Last edited by Lek on Tue Feb 03, 2009 6:12 am; edited 1 time in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 03, 2009 6:11 am    Post subject: Reply with quote

All about the #REF! error: [Tutorial] Absolute, relative and mixed references

LOOKUP will never work when you mis-use a spreadsheet as database. LOOKUP is designed to work in ordered mode only.
In this mode 1.5 matches at the last position where the value is <=1.5 in an ascending ordered vector. LOOKUP(1.5; {1;2}) => 1

Typical example: return 6 grades "A" to "F" from points 0 to 100.
0 F
20 E
35 D
50 C
67 B
85 A

VLOOKUP(25;range;2) => E [ordered mode=1 is default]
VLOOKUP(25;range;2;1) => E
LOOKUP(25;col1;col2) => E [implicit ordered mode]
INDEX(col2;MATCH(25;col1)) => E [ordered mode=1 is default]
INDEX(col2;MATCH(25;col1;1)) => E
Never returns #NA except for search values smaller than first value.

Unordered mode:
INDEX(col2;MATCH(25;col1;0)) => #NA [no 25 in col1 in unordered mode]
VLOOKUP(25;range;2;0) => #NA [no 25 in col1 of range in unordered mode]
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org


Last edited by Villeroy on Tue Feb 03, 2009 6:46 am; edited 2 times in total
Back to top
View user's profile Send private message
Lek
Power User
Power User


Joined: 20 Oct 2006
Posts: 73

PostPosted: Tue Feb 03, 2009 6:31 am    Post subject: Reply with quote

I understand.

Also understand that a database would be the best course, unfortunately I have not the time to get that far (tried in the past...that will take me some time)....and if this were a more complex data need, would definitely take the time to do so (or hire some one to do so...lol)

The way this is being used...The data to be searched for will always be provided and will always match Identically to where it is being searched....so I believe the 1.5 example therefore would not occur or be an issue, but I do understand what you are showing in the example....or so I think I do.

I will print this thread off and go through your prior post more.
Lost me on some things...and when I have more time will want to figure it all out if just to learn more and keep for reference.

Thank You.
Between this and the referenced (linked) posts, this has been very helpful.
(saved them all)
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 03, 2009 7:03 am    Post subject: Reply with quote

OK, save this as well:
"Easy spreadsheets"
Apart from the glitches with modes, numbers (rounding errors) and strings (Tools>Options...CAlc>Calcultion, case, whole cell, regex):
- A spreadsheet-grid has no start nor end VLOOKUP(x; $A$1:$B$999;5;0) will fail when you append data below row #999 without expanding the range (insert new rows for new data or adjust all references).
- Unexperienced users may also destroy the list by sorting when not all the columns are selected when sorting.
- A spreadsheet has no concept of unique identities. VLOOKUP(x; $A$1:$B$999;5;0) will always return from the first matching row, even if there are duplicates. Ordered mode VLOOKUP(x; $A$1:$B$999;5;1) will always return from the last matching row, even if there are duplicates.
- A spreadsheet accepts all types of values in the same cell. Text-value "123" is not the same as number 123 even if they look like the same value.

All this is not an issue when using a database, which accepts only one distinct type of data in a column, may or may not accept empty values in a column, may or may not accept duplicates in a column, where the list has a start and an end with labels separated from data and where you simply draw a line between two fields in two tables in order to create a relation between the two fields where the main table will only accept existing entries from the details table.

It's very easy to get wrong results from spreadsheets
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Lek
Power User
Power User


Joined: 20 Oct 2006
Posts: 73

PostPosted: Tue Feb 03, 2009 7:28 am    Post subject: Reply with quote

- Spreadsheet grid: I understand this....why i have made range +250 in addition to the material list that is most likely...should never exceed 1000 items.

- I am only user of the calc...but will be set as a template...worst some one could do is screw up the one quote, not the actual data list.

- Every listed cell for search is unique...there are no duplicates....there is only one match for each search and no way to alter the searched item n the formula as that will be pre-set based on the calculation choices.

- not sure how cell format will affect...but so far so good...I am keeping on eye on all results....and testing to make sure they are the right ones.


Honestly, I really wish I fully understood how to do this in a database... where it would not take me so much time to learn...hell, took me 4-5 hours just to re-comprehend the silly look ups....lol...Really not sure what it takes to make the data sheet into a database, then be able to pull the data into the spreadsheet for use as pricing. (so do not understand data pilot)

I likely only have a few more days over the course of the next 4-6 weeks to do this and then it all has to wait till next year.... or the odd off day. A lot of that time is still the actual calculations and layout. The calculations are easy...the damned multiple scenarios take so damned long....semi-novice, remember...lol.

I have a lot of started projects that never get finished...but I keep at it.

Eventually....
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 03, 2009 7:37 am    Post subject: Reply with quote

In 4 to 5 hours you can really learn so much more fundamental stuff about databases (any ralational SQL-database in general, not Base specifically) that it would be very easy to set up such a thing in Base or any other database tool for the same purpose.
The databases you can create in Base are nasty toys. I don't consider them to be real databases, but they can handle all this easily.

Spreadsheets are the sweet poison of computing. Everything looks so clean and easy.
Burns Statistics -- Spreadsheet Addiction, which is well researched phenomenon: http://www.eusprig.org/
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Lek
Power User
Power User


Joined: 20 Oct 2006
Posts: 73

PostPosted: Tue Feb 03, 2009 8:42 am    Post subject: Reply with quote

Hmmm...tempting..took a quick look, set up the price list I have as a base file (converted spreadsheet).....played a bit, just do not have the understanding....

.....do not comprehend how to set the calculation of everything that is being done in the spreadsheet, in a base, so that i just enter my measurements in a table and then have it compute my totals based on some drop down menu choices and additional numbers needed. (These calculations get a wee bit complex as sections can have a few additions that need to be on individual line items as they can vary in amount needed per grade designated, doable with my limited knowledge, in calc.,)

Nor do I understand what benefit a price list in base form would be if I have to import it into the spreadsheet and then use the same formulas in the spreadsheet I am using now based on the imported data.

Just have no education in base other than using it to auto fill customers information in proposal forms we use in Writer (and am sure what i set up is quite novice like....though it works).

Wish it was as simple as a few hours....but without the understanding that so many of you here have...I will have to make do for the time being as is.

I have a very simple spreadsheet I have been using for estimating this product line......just trying to adapt it (actually overhaul it) to automatically find the prices from a master list instead of manually inserting the prices for each estimate as well as being a little more versatile and less repetitive in materials needed.

I do appreciate your time, help and advice......and will hopefully, eventually work on understanding bases.....a good book maybe....lol.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 03, 2009 10:00 am    Post subject: Reply with quote

Sorry, you will not succeed if you paste your spreadsheet data into Base. It's possible to import more than one table and implement a relation between the two, but that is not as trivial as copy and paste.
The volunteers on this forum or the other one could hold one database seminar almost every day.

Quote:
Nor do I understand what benefit a price list in base form would be if I have to import it into the spreadsheet and then use the same formulas in the spreadsheet I am using now based on the imported data.

A database can enforce a certain degree of consistancy. Nothing prevents you typing a wrong number or text but it can enforce many things by just rejecting invalid values.
Storing all constant values (not formulas) in a database lets your spreadsheet operate on consistant values with a start and end.

The "Bibliography" example is a very loose example without enforcing anything, but it has a start and an end:
-- Drag table "biblio" of the Bibliography from the left pane of the beamer (F4) onto A1 of a blank spreadsheet.
-- Edit the resulting database-range (Data>Define..."Import1") and add option "insert/remove cells" (below [More...])
-- Append a calculated column directly behind the last imported column, say AF2 =COUNTA(A2:AE2).
-- Append and remove arbitrary database records and refresh the import range. (click single cell, Data>Refresh)
-- Watch how the adjacent formulas adjust to the resizing range.

Calculations in a database:
A spreadsheet trying to explain how to get sums and counts using more than one criteria column by means of function SUMPRODUCT (multiple COUNTIF and SUMIF): http://user.services.openoffice.org/en/forum/download/file.php?id=2939 and how this function works internally. A very frequently asked question in all spreadsheet related groups.
The same and much more implemented in a database: http://user.services.openoffice.org/en/forum/download/file.php?id=2950
A database with horizontal calculation V=W*H*L: http://user.services.openoffice.org/en/forum/download/file.php?id=1266
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
eduzs
OOo Advocate
OOo Advocate


Joined: 07 Feb 2005
Posts: 356
Location: RJ, BRAZIL

PostPosted: Thu Feb 05, 2009 5:49 pm    Post subject: Reply with quote

I need to get the cell name where is the "X" in a cellrange.
Ex.: Wheres is "X" in B1:F10 ? "X" is in "D3" for example.
Whats the function to do that?
thanks
_________________
BrOffice.org 3.2.1
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group