| Author |
 Replies: 238 / Views: 8,105 |
|
|
|
Pillar of the Community
 United States
4000 Posts |
Quote:
HNL HRK HTG HUF IDR AOA AZN BAM BDT BGN 1/0! I can't believe one in the right place hasn't happened randomly yet. |
|
Pillar of the Community
United States
4000 Posts |
Here's another way rikcando, using a horizontal lookup.
I'll use cell AA8 because that was the first cell I seen a true evaluation.
In cell AA8 =IF(ISNA(HLOOKUP(E8,$C$3:$L$3,1,FALSE)),0,1), this way you use an absolute reference to your "control/answer range", but a relative reference to your "guess range".
The formula says IF (my guess) IS NOT in row 3, then return false (which means the ISNA() statement is true), else return true (meaning the ISNA() statement is false).
Edited by Scooby Due
08/07/2010 9:51 pm
|
|
Pillar of the Community
United States
2669 Posts |
I tried using vlookup's for conditional formatting today to automatically change the yellow/red backgrounds and text colors based on whether something was (a) eliminated or (b) had been guessed yet. One master list that items get deleted from as they are eliminated controls those, then another sheet with the list of guesses populates a different section where the real tallies are done. Worked out pretty well.. saves time color-coding by hand and cuts down on the potential for human error.
Thanks for the ISNA mention - I should probably use that instead of ISERROR because if it comes back something like #REF that means the formula itself is wrong.
|
|
Valued Member
Canada
287 Posts |
Quote:
AA8 =IF(ISNA(HLOOKUP(E8,$C$3:$L$3,1,FALSE)),0,1) Have you tried this? According to standard definition the table array must be two or more rows of data that is sorted in ascending order. You only have one row. |
|
Pillar of the Community
United States
4000 Posts |
I also use the conditional formatting, except with the hlookup checking the control row. They turn green if the currency is in the list, blue if they are in the right place, and stay black if they are not in the list at all.
|
|
Valued Member
Canada
287 Posts |
I can just picture weavus putting down his pencil and paper and having a real good laugh at us.
|
|
Pillar of the Community
United States
4000 Posts |
Not if you are using a horizontal lookup. The rule you are referring to applies to vertical lookups.
|
|
Pillar of the Community
United States
4000 Posts |
You can copy and paste that formula right into cell AA8 and it should work like a charm.
|
|
Pillar of the Community
United States
4000 Posts |
Also, the FALSE part in this section of the formula "HLOOKUP(E8,$C$3:$L$3,1,FALSE)" tells it whether the data is sorted or not. If you used TRUE, the data MUST be sorted.
|
|
Pillar of the Community
United States
4000 Posts |
Quote:
I can just picture weavus putting down his pencil and paper and having a real good laugh at us. And then win it! |
|
Pillar of the Community
United States
539 Posts |
I'm not really laughing. I am smiling very, very big though. I'm amazed at your skills with excel. I don't know what you all do for a living but you certainly have a command of excel. And I am working on that last point. I just hope my pencil doesn't break during a gigantic calculation.
|
|
Valued Member
Canada
287 Posts |
So if true that it is false then return nothing, but if false is not true then we get a value. hmmm, interesting inside out way of looking at it.
This remonds me of The Parable of the Two Programmers. Google it if you have not read it before.
btw, I like the 'OR' method best so far but I'll be testing the HLOOKUP method for speed and flexibility.
Edited by rikcando
08/07/2010 10:41 pm
|
|
Pillar of the Community
United States
4000 Posts |
The ISNA() function tests whether the value is in the list or not. If it is not, then the statement is TRUE. The IF() statement is the part that flips it to return the values that we need for SUM(), etc...
|
|
Pillar of the Community
United States
2669 Posts |
Most formula creation is born after this thought: "Gee, this is such a pain in the rear, can I make it do it automatically..?!" ... And at least with pencil and paper you can erase.. with Excel, once you've saved the file you can't "undo" - so if you've gone and made a really boneheaded mistake (such as misreading a 1/0 as 0/0 and deleting 10 currencies from your master list, *cough*), you have to either start over or plug everything back in. I just realized it's midnight there again soon - being on west coast time has some upside, after all  |
|
Valued Member
Canada
287 Posts |
Quote:
The ISNA() function tests whether the value is in the list or not. Not quite but same answer. It tests to see if the HLOOKUP returns 'N/A', which it will do if the search value is not found. |
| |
Replies: 238 / Views: 8,105 |
First Page
Previous Page
Posted 08/07/2010 9:49 pm
