Excel Question #46 - Page 7

Alex · 12-11-2010, 07:35 AM

How will the cell that triggers the formula be formatted? That is, what exactly would be your formula's input? A single cell with value apple/Ralph? One cell says apple and the next says Ralph?

Anyway, for this array (cell A1 is the blank top left)

Code:

          apple      banana      orange
Ralph     a          b           c
Fred      d          e           f
George    g          h           i

And for simplicity sake let's say that the first half of what you're looking for (e.g. George) is in cell A12 below that array and the second half (e.g. banana) is in B12.

Then in C12 this formula would return h

Code:

=INDEX(A1:D4,MATCH(B12,A1:A4,0),MATCH(A12,A1:D1,0))

If the input were actually in one cell you'd have put a formula into where this says "B12" and "A12" to parse the input first.

Alex · 12-16-2010, 07:00 AM

Hey Kevy, I'm just curious. Did you get a chance to try this and were the instructions clear?

Kevy Baby · 12-17-2010, 03:04 PM

Quote:

Originally Posted by Alex

Hey Kevy, I'm just curious. Did you get a chance to try this and were the instructions clear?

I didn't, but I have now

I did find a minor error in your formula: you had switched the A12 and B12 references in the Match portion of the formula

=INDEX(A1:D4,MATCH(B12,A1:A4,0),MATCH(A12,A1:D1,0) ) <-- you had
=INDEX(A1:D4,MATCH(A12,A1:A4,0),MATCH(B12,A1:D1,0)) <-- this worked

When I applied it to my own needs, I ran into trouble because I was swapping the two matches (basically, trying to make the first match on the horizontal axis and the second on the vertical). Once I found my error, it worked - thank you.

Now to move it past the testing phase and put it into real world!

Alex · 12-17-2010, 03:10 PM

Sorry about that, glad you found the issue. I probably had the right formula (since I pasted from Excel) but described it wrong.

Kevy Baby · 02-01-2011, 06:23 PM

Here's another lookup challenge that I am not sure how to handle.

I get USPS and UPS Zone Charts in a format similar to this:

Code:

Zip Code    Zone
  005-212     8
  214-268     8
  270-324     8
  325         7
  326-342     8
  344         8
  346-347     8
  349         8
  350-352     7
  354-359     7
  360-363     8

If some of the Three Digit Zips are shown in a range, is there a way to have Excel look in the range? How would I tell Excel that the Zone for 289 is 8?

Ghoulish Delight · 02-01-2011, 07:41 PM

My first notion is this (not an easy 1-cell solution unfortunately). The following code will return the two end points of the range (assuming the range is in cell A1:

Left(A1,FIND("-",A1)-1)
Right(A1,Find("-",A1)-1)

Those are text results, and the only way I've found so far to get a numeric value is to put those formulas in ohter cells (we'll use A2 and A3), then use =Value()

So then, for a value of 289 in Cell B1 you can do a =IF(VALUE(A2)<=B2,IF(B2<=VALUE(A3),1,0),0)

You'll get a 1 if it is in the range, 0 if not.

Kevy Baby · 10-04-2011, 07:01 PM

I came here to ask a question about merging multiple Excel spreadsheets, but I did a little more digging and found a plug in which does it AND learned all about the DATEVALUE function.

I love learning new things in Excel

Betty · 02-16-2012, 02:31 PM

I have a spreadsheet I got online and tweaked a bit for my husband to help him calculate overtime hours. Unfortunately it's not working right and now is not the time for this to be happening. ARGH! (It is SO one of those days - starting last night with the dead battery, continuing with today's flat tire and now this. While I won't go into details - the timing of this couldn't be worse.)

I'm hoping one of you excel-lent excel-lers will be able to look at it and go - add this and "ta-da" it works.

Here is a link to screen shots: https://picasaweb.google.com/1106550...JHNsqmhrKD1Kg#

Or I could send the file if that's easier.

It should be showing overtime on saturday if M through S has 8 hours. It's not though.

Moonliner · 02-16-2012, 03:10 PM

Quote:

Originally Posted by Betty

I have a spreadsheet I got online and tweaked a bit for my husband to help him calculate overtime hours. Unfortunately it's not working right and now is not the time for this to be happening. ARGH! (It is SO one of those days - starting last night with the dead battery, continuing with today's flat tire and now this. While I won't go into details - the timing of this couldn't be worse.)

I'm hoping one of you excel-lent excel-lers will be able to look at it and go - add this and "ta-da" it works.

Here is a link to screen shots: https://picasaweb.google.com/1106550...JHNsqmhrKD1Kg#

Or I could send the file if that's easier.

It should be showing overtime on saturday if M through S has 8 hours. It's not though.

For starters, Monday is in column 'B' but the formula starts in column 'C'. Update your SUM function. (Note 'SUM' appears twice in the first image.)

Alex · 02-16-2012, 03:02 PM

I'm not entirely sure of the goal, so is this correct?

1. You enter hours worked on a day on Row 4. If this is 8 hours or less it automatically appears on row 5 as regular hours. If it is more than 8 hours the first 8 hours goes under regular pay and the rest goes under OT. (Not sure what DT is.)

2. Once the total hours in a week reaches 40 hours then all hours after that go to OT.

Is that correct?

12-11-2010, 07:35 AM	#1
Alex . Join Date: Feb 2005 Posts: 13,354	How will the cell that triggers the formula be formatted? That is, what exactly would be your formula's input? A single cell with value apple/Ralph? One cell says apple and the next says Ralph? Anyway, for this array (cell A1 is the blank top left) Code: apple banana orange Ralph a b c Fred d e f George g h i And for simplicity sake let's say that the first half of what you're looking for (e.g. George) is in cell A12 below that array and the second half (e.g. banana) is in B12. Then in C12 this formula would return h Code: =INDEX(A1:D4,MATCH(B12,A1:A4,0),MATCH(A12,A1:D1,0)) If the input were actually in one cell you'd have put a formula into where this says "B12" and "A12" to parse the input first.
	Submit to Quotes

12-16-2010, 07:00 AM	#2
Alex . Join Date: Feb 2005 Posts: 13,354	Hey Kevy, I'm just curious. Did you get a chance to try this and were the instructions clear?
	Submit to Quotes

02-01-2011, 06:23 PM	#5
Kevy Baby Chowder Head Join Date: Jan 2005 Location: Yes Posts: 18,500	Here's another lookup challenge that I am not sure how to handle. I get USPS and UPS Zone Charts in a format similar to this: Code: Zip Code Zone 005-212 8 214-268 8 270-324 8 325 7 326-342 8 344 8 346-347 8 349 8 350-352 7 354-359 7 360-363 8 If some of the Three Digit Zips are shown in a range, is there a way to have Excel look in the range? How would I tell Excel that the Zone for 289 is 8? __________________ The thing about quotes on the internet is that you cannot verify their validity. - Abraham Lincoln
	Submit to Quotes

02-01-2011, 07:41 PM	#6
Ghoulish Delight I Floop the Pig Join Date: Jan 2005 Location: Alternative Swankstyle Posts: 19,348	My first notion is this (not an easy 1-cell solution unfortunately). The following code will return the two end points of the range (assuming the range is in cell A1: Left(A1,FIND("-",A1)-1) Right(A1,Find("-",A1)-1) Those are text results, and the only way I've found so far to get a numeric value is to put those formulas in ohter cells (we'll use A2 and A3), then use =Value() So then, for a value of 289 in Cell B1 you can do a =IF(VALUE(A2)<=B2,IF(B2<=VALUE(A3),1,0),0) You'll get a 1 if it is in the range, 0 if not. __________________ 'He who receives an idea from me, receives instruction himself without lessening mine; as he who lights his taper at mine, receives light without darkening me.' -TJ
	Submit to Quotes

10-04-2011, 07:01 PM	#7
Kevy Baby Chowder Head Join Date: Jan 2005 Location: Yes Posts: 18,500	I came here to ask a question about merging multiple Excel spreadsheets, but I did a little more digging and found a plug in which does it AND learned all about the DATEVALUE function. I love learning new things in Excel __________________ The thing about quotes on the internet is that you cannot verify their validity. - Abraham Lincoln
	Submit to Quotes

12-17-2010, 03:10 PM	#4
Alex . Join Date: Feb 2005 Posts: 13,354	Sorry about that, glad you found the issue. I probably had the right formula (since I pasted from Excel) but described it wrong.
	Submit to Quotes

02-16-2012, 02:31 PM	#8
Betty Kicking up my heels! Join Date: Jan 2005 Location: The Silver State Posts: 3,783	I have a spreadsheet I got online and tweaked a bit for my husband to help him calculate overtime hours. Unfortunately it's not working right and now is not the time for this to be happening. ARGH! (It is SO one of those days - starting last night with the dead battery, continuing with today's flat tire and now this. While I won't go into details - the timing of this couldn't be worse.) I'm hoping one of you excel-lent excel-lers will be able to look at it and go - add this and "ta-da" it works. Here is a link to screen shots: https://picasaweb.google.com/1106550...JHNsqmhrKD1Kg# Or I could send the file if that's easier. It should be showing overtime on saturday if M through S has 8 hours. It's not though. __________________ Nee Stell Thue
	Submit to Quotes

02-16-2012, 03:02 PM	#10
Alex . Join Date: Feb 2005 Posts: 13,354	I'm not entirely sure of the goal, so is this correct? 1. You enter hours worked on a day on Row 4. If this is 8 hours or less it automatically appears on row 5 as regular hours. If it is more than 8 hours the first 8 hours goes under regular pay and the rest goes under OT. (Not sure what DT is.) 2. Once the total hours in a week reaches 40 hours then all hours after that go to OT. Is that correct?
	Submit to Quotes