[Solved] Is this even possible with Calc?

[CaliRay]

My formula writing skills are very limited and I have what, for me, is a very complex formula to write. I hope someone here can help with this.

Here's the situation. I have two columns of numbers and based on a test of each pair of numbers in a row, I need to fill a third cell on that row with one of four possible words. Below are listed the conditions to test and the needed result. Every set of numbers will fall somewhere in these nine tests.

1) If cell A1 is between 0 and 499 and cell B1 is between 0 and 10000, I want to write "GOOD" in cell C1.
2) If cell A1 is between 500 and 999 and cell B1 is between 0 and 10000, I want to write "GOOD" in cell C1.
3) If cell A1 is 1000 or more and cell B1 is between 0 and 10000, I want to write "EXCELLENT" in cell C1.
4) If cell A1 is between 0 and 499 and cell B1 is between 10001 and 20000, I want to write "POOR" in cell C1.
5) If cell A1 is between 500 and 999 and cell B1 is between 0 and 10000, I want to write "FAIR" in cell C1.
6) If cell A1 is 1000 or more and cell B1 is between 10001 and 2000, I want to write "GOOD" in cell C1.
7) If cell A1 is between 0 and 499 and cell B1 is between 20001 and 50000, I want to write "POOR" in cell C1.
8) If cell A1 is between 500 and 999 and cell B1 is between 20001 and 50000, I want to write "POOR" in cell C1.
9) If cell A1 is 1000 or more and cell B1 is between 20001 and 50000, I want to write "FAIR" in cell C1.

I've tried to construct this formula several times by building up each test using IF with AND, but can't get it together. One problem I can't solve is how to return something other than "TRUE" or "FALSE" using the IF/AND tests.

I really appreciate any help offered for this problem.

Regards, CaliRay

[squenson]

For the first case, write in C1:
=IF(AND(A1>=0;A1<=499;B1>=0;B1<=10000);"GOOD";"")
This should either return GOOD or an empty string

Write similar IFs for the other 8 cases and put them all with + sign in between in C1. So your final formula looks like:
=IF(AND(A1>=0;A1<=499;B1>=0;B1<=10000);"GOOD";"") + IF(...) + IF(...)

As only one IF is true at a time, you concatenate 8 empty strings with one correct evaluation. I find this approach easier than nested IFs that will quickly become unreadable. Once you have your final formula, you can post it here for future reference.

[Villeroy]

Take a look at my MATCH_0_1.ods where I use lookups in 2 different ways using a vertical ordered vector (yellow) and a green unordered array of text values to look up a resulting number.

In your case two of the yellow vectors are required (numeric and sorted) with the resulting text values in the blue range.

[CaliRay]

So this is a little more clear, I'm testing cells B2 and C2. Cell B2 contains 1000 and C2 contains 8950. To repeat, here is the first arguement. It returns a null just as expected.

Arguement 1) If cell B1 is between 0 and 499 and cell C1 is between 0 and 10000, I want to write "GOOD" in cell D1.
=IF(AND($B2>=0;$B2<=499;$C2>=0;$C2<=10000);"GOOD";"")

Arguement 2) If cell B1 is between 500 and 999 and cell C1 is between 0 and 10000, I want to write "GOOD" in cell D1.
+ IF(AND($B2>=500;$B2<=999;$C2>=0;$C2<=10000);"GOOD";"")

But, when I add the second arguement, I get a 502 error.

Here's the complete formula so far.
=IF(AND($B2>=0;$B2<=499;$C2>=0;$C2<=10000);"GOOD";"") + IF(AND($B2>=500;$B2<=999;$C2>=0;$C2<=10000);"GOOD";"")

I don't know the proper way to connect the arguements together. The example (=IF(AND(A1>=0;A1<=499;B1>=0;B1<=10000);"GOOD";"") + IF(...) + IF(...))
shows a space, then a +, then another space before the IF. I also notice a lack of = in each of the arguements after the first one.

I read the explanation for the 502 error, but it didn't say anything that I saw for why the formula did not return another null for the second arguement.

Sorry to be so uninformed, but I don't work with these things.

Thanks again for the help. I will be sure to help others when I can.

Regards, Ray

[squenson]

Sorry, my mistake, the concatenation sign for strings is &, not +
So your formula should look like:
= IF(...) & IF(...) & IF(...)

The spaces are added for visibility, they are not required.

[acknak]

I have to agree with Villeroy: the better approach is to use a lookup table--writing out the logic is just too easy to make mistakes, and the table makes it very easy to see what results go with what scores.

Did you realize that you made a mistake writing out the conditions? Cases 2 and 5 have the same inputs but expect a different output.

Here's a sheet that implements your scheme (with an assumed correction), using Villeroy's MATCH() technique to fetch the result from a table:

[CaliRay]

Thanks again. I changed the + to & and the formula works perfectly. Now I need to find where to put this for others to use.

Regards, Ray

[CaliRay]

I have to agree with Villeroy: the better approach is to use a lookup table--writing out the logic is just too easy to make mistakes, and the table makes it very easy to see what results go with what scores.

Did you realize that you made a mistake writing out the conditions? Cases 2 and 5 have the same inputs but expect a different output.

Here's a sheet that implements your scheme (with an assumed correction), using Villeroy's MATCH() technique to fetch the result from a table:

You're right...Number 5 should have read
5) If cell A1 is between 500 and 999 and cell B1 is between 10001 and 20000, I want to write "FAIR" in cell C1.

I looked at the lookup spreadsheet you sent, but haven't studied it enough to make any sense of it. I am definitely not a programmer and have to think hard to even arrive at the necessary logic to create these things. That's especially hard when the Open Office help seems to be written for more knowledgable people.

From looking at the sheet labeled "Main", I don't see how it is checking the table on the second sheet to know what to write as the answer. I'm not familiar with coding these things, but I expected to see on the Main sheet some reference to the Ratings table sheet.

I'll try to work it out. Thanks so much for making the effort to help.

Regards, Rar

[acknak]

Yes, it's a bit much to digest--but well worth it if you need to work with spreadsheets regularly.

One technique used in the sample is called a "named range". That simply lets you assign a name to a group of cells, then to use the name instead of the cell address. That allows a formula to be easier to understand--if you choose a name that tells something about the purpose of the cells.

So, in the formula on the "Main" sheet: =OFFSET(Ratings;MATCH(A2;ColA;1);MATCH(B2;ColB;1);1;1), "Ratings" is a name that stands for the address $'Ratings Table'.$A$1 which is on the other sheet. Similarly for "ColA" and "ColB".

I don't know if those names are accurate--you can (and should) change them to something that fits with your purpose.

You can see (and change) the range names with Insert > Names > Define. You can jump to a named range with the "Name Box" dropdown to the left of the formula entry area.

Here's a revision of the sample sheet, with some further annotation and explanation. Look at the second sheet, where everything is together. The "Main" sheet just wraps up the same calculation in a compact form.

[CaliRay]

Just one more thing about this lookup stuff. The lookup solution I was sent works perfectly; however, I forgot to include one other case. I need to expand the lookup function to account for searches where column B (Google exact phrase match) exceeds 50,000.

I'm analyzing counts for Google searches AND number of pages with an exact phrase such as "beetle" where there are monthly searches for a wide variety of "beetle" related keywords.
Example for the keyword phrase "vw parts":
Search Count (column A) Exact Phrase Count(column B)
40,500 1.060,000

This keyword is "too competitive" because there are too many exact match pages in Google.

I hope I have made this clear. Simply put, I need the formula extended to include another test for the Column B value to exceed 50,000.

Thanks again. BTW as I am typing this I see your answer with a detailed and combined ods file. Thanks.

Ray

[acknak]

I'm not sure I understand. Do you mean you don't want the formula to fail if the count exceeds 50,000, or do you mean that you need to add a new category of 20,000-50,000?

I haven't looked at the logical formula, but I'm sure it can be modified to handle either of those situations. The lookup table already works with counts of 50,000 or more: anything over 20,000 matches the last column.

If you need another category, you can easily modify the lookup table to include one; you will have to modify the range names to match, though.

Please tell me this is not some kind of SEO.

[Villeroy]

However you like to modify your 2-D lookup range, make shure to check menu:Tools>Options...Calc>General:"Expand References..."
The insert new cells within, directly below or directly right of A1:D4.
For instance, select A5:D5, call menu:Insert>Cells... [x]Shift cells down ...
will shift all references to range A1:D4 towards range A1:D5. The named reference "ColA" will refer to A1:A5.

[CaliRay]

I can't find any reference material here or anywhere else
that explains, in DUMMY DETAIL, how the MATCH and
OFFSET formulas work. I really want to understand how
this formula processes the data. Any help is appreciated.

I have 2 columns of numbers. I need to test the 2 numbers
in a particular row to see if they fall within certain parameters.

I set this up so that the examples are formatted and look good
but when I see how it will look after this message is submitted,
the tables don't have proper spacing. I apologize, but don't see
any way to fix it so that the columns are aligned properly. I also
can't find how to insert images to show my spreadsheet.

I have been helped with this formula
=OFFSET(Ratings;MATCH(A2;ColA;1);MATCH(B2;ColB;1);1;1)
that works for this example where the numbers, for example
are: Col A is 1253 and Col B is 22000 and the value of "FAIR"
is returned in column C.

ColA/ColB 0 10001 20001
0 GOOD POOR POOR
500 GOOD FAIR POOR
1000 EXCELLENT GOOD FAIR

But I need to modify the formula
=OFFSET(Ratings;MATCH(A2;ColA;1);MATCH(B2;ColB;1);1;1)
to work where there is an additional test for values of 50001
or greater that returns, in Col C a value of "TOO COMPETITIVE".

I've tried everything I can think of, but since I don't fundamentally
understand how these formulas work, I haven't been able, through
trial and error, to create a modified formula that works for the
following example.

ColA/ColB 0 10001 20001 50001
0 GOOD POOR POOR TOO COMPETITIVE
500 GOOD FAIR POOR TOO COMPETITIVE
1000 EXCELLENT GOOD FAIR TOO COMPETITIVE

I hope someone can help me to find reading material that explains in detail
how this OFFSET and MATCH works so I can become better in using these
types of functions.

Regards and thanks, CaliRay

[acknak]

I'm glad you asked; and don't say "DUMMY", we all start out needing to learn.

MATCH is easy; it resembles something we do any time we use some kind of reference book or table. MATCH takes a value and looks for a matching value in a range of cells. The result is the position in the search range where the matching value was found.

OFFSET is easy too, it's just not quite as familiar.

Suppose you lived in a city, not far from the train station, and a friend is coming, by train, to visit. In that situation, you might give directions like, "I live 2 blocks East and 3 blocks North of the train station". You could give the exact street address, but your visitor would still need to determine how to get from the train station to your house, so you might as well just provide the directions relative to the station.

That's how OFFSET works: instead of referring to an absolute address (B2), it starts with an absolute address (the train station) and "moves" a number or rows and columns (city blocks) away from the starting place. E.g. if you OFFSET one row and one column from B2, you're at C3. The result of the OFFSET is the same as if you wrote "C3" in the formula, except the result is dynamic--it can change because the row and column offsets can change; the reference is calculated, not fixed.

Here's an other sample spreadsheet that may help you see what's happening; if it doesn't get you to the point that you can get your modified table working, just yell.