DPChallenge: A Digital Photography Contest You are not logged in. (log in or register) 
 

DPChallenge Forums >> General Discussion >> OT: Need an Excel expert
Pages:  
Showing posts 1 - 9 of 9, (reverse)
AuthorThread
 08/26/2006 01:05:52 PM · #1
I know this is way off-topic, but I desperately need the help of an Excel expert..

My source document is a list of activities for an upcoming event that an outdoors group I volunteer with is hosting. The source document is in columns A to BT, inclusive. The only changes I've made to those colums are to add color-coding by timeslots, edit the column names in row 1 to make them more friendly for the labels I'm trying to produce. Also, I've copied row 1 to row 200 to make my HLOOKUPS of the event names work.

In columns CA:CJ, I've added columns to do an HLOOKUP of what event each person signed up for. Each person should have timeslots where they didn't sign up for anything, in which case the relevant cell should be blank. The end goal is to feed the names (in columns A and B) and the events (in columns CA:CJ) into a mail merge to produce labels for each person's registration packet.

The problem that I'm having is that my formulas seem to be broken, and the correct events are not filling in consistently.

Would someone mind taking a look at this and tell me where I have gone astray? The spreadsheet is at //www.clubjuggle.com/aliyah.xls.

Thanks,
Terry
 08/26/2006 01:18:10 PM · #2
I may be completely off the mark, but in all your formulas there's the C-something:K$200 which to me doesn't look like a normal row number or as if something was taken out of that row in the other table.
 08/26/2006 01:20:57 PM · #3
Originally posted by letenele:
I may be completely off the mark, but in all your formulas there's the C-something:K$200 which to me doesn't look like a normal row number or as if something was taken out of that row in the other table.


The $ tells Excel that the row number is absolute. This is important when copying the formula... for example... if I have a range of C2:K$200, and I copy the cell down one row, it becomes C3:K$200. Without the $, it becomes C3:K201.

~Terry
 08/26/2006 01:24:18 PM · #4
Looks to me like you have true/false argument for when sum is 0, or >1, but not for when sum is 1.

Message edited by author 2006-08-26 13:25:50.
 08/26/2006 01:25:32 PM · #5
=IF(SUM(L2:S2)=0," ",IF(SUM(L2:S2)>1,"ERROR",HLOOKUP(1,L2:S$200,201-ROW(),0)))

Put that ,0 in after row()

Message edited by author 2006-08-26 13:25:52.
 08/26/2006 01:30:06 PM · #6
Originally posted by jhonan:
=IF(SUM(L2:S2)=0," ",IF(SUM(L2:S2)>1,"ERROR",HLOOKUP(1,L2:S$200,201-ROW(),0)))

Put that ,0 in after row()


w00t! That did it!

Any idea why I would need that? I'm searching for 1, and signed-up events are indicated by a 1. In theory, it shouldn't be needed.

Thanks,
~Terry
 08/26/2006 01:32:33 PM · #7
I would think you want want >=1, instead of just >1, but if it's working then don't fix it! :)
 08/26/2006 01:32:58 PM · #8
Originally posted by ClubJuggle:
Originally posted by jhonan:
=IF(SUM(L2:S2)=0," ",IF(SUM(L2:S2)>1,"ERROR",HLOOKUP(1,L2:S$200,201-ROW(),0)))

Put that ,0 in after row()


w00t! That did it!

Any idea why I would need that? I'm searching for 1, and signed-up events are indicated by a 1. In theory, it shouldn't be needed.

Good question! - I use VLOOKUP() all the time, and put the ,0 at the end out of habit. It just ensures it finds the correct match.

If you leave it at ,1 (the default) it can have some strange results. Not exactly sure why though.
 08/26/2006 01:42:30 PM · #9
It's strange, if you use say the letter 'y' instead of the number '1' the function works fine. Use the number '1' though and it can't find an exact match.

=HLOOKUP("y",L4:S$200,201-ROW()) <-- Finds it
=HLOOKUP(1,L4:S$200,201-ROW()) <-- Doesn't find it, but instead finds closest approximate value, i.e. the wrong one!

I found this webpage, which might explain it;

//www.louisepryor.com/showTopic.do?topic=48

Approximate or exact?

You should always specify the optional fourth argument to these functions, so that it is obvious which sort of lookup you intend.

Whether you use an approximate or exact lookup will, of course, depend on the use to which you are going to put the values that you are looking up. However, there are some situations that you should avoid:

* Don't use an approximate lookup if the lookup table isn't sorted in ascending order by the values in the leftmost column (VLOOKUP) or topmost row (HLOOKUP).
* Don't use an exact lookup if your lookup values are floating point numbers (ie, any numbers with decimal points). This is because floating point numbers have to be exactly the same in order to match: 4.9999999999999999 won't match 5. Remember that even if a number is displayed as 5.0, it may actually be 4.9999999999999999.
* Be very careful when using floating point numbers even with approximate lookups. 4.9999999999999999 is less than 5, so Excel wouldn't find the correct row even with an approximate lookup.

Message edited by author 2006-08-26 13:43:35.
Pages:  
Current Server Time: 09/23/2025 02:50:30 AM

Please log in or register to post to the forums.


Home - Challenges - Community - League - Photos - Cameras - Lenses - Learn - Help - Terms of Use - Privacy - Top ^
DPChallenge, and website content and design, Copyright © 2001-2025 Challenging Technologies, LLC.
All digital photo copyrights belong to the photographers and may not be used without permission.
Current Server Time: 09/23/2025 02:50:30 AM EDT.