Excel help needed...

[pit29]

Hi,

I want to do something in Excel now and then and always used some kind of workaround - but this time I decided to ask for help.

Basically, it's a problem with referencing cells. Say, I have two collumns with values. I want to have another cell that gives me the value in collumn B that is next to the minimal value in collumn A. That should not be so hard, but somehow I can't find out how to do that.

Another issue is rearranging cells. Say I have values in A1, C1, E1, G1 and so on - is there an easy way to get them to C5, C6, C7,... using some formulas that I can use the fill down command with?

I'd greatly appreciate any suggestions. Bonus points if that works in Numbers as well.

Pit

 

[reclusivemonkey]

To answer your second question, you can use the "Transpose" box in paste special. If you copy a series of numbers in A1, B1, C1, D1, E1 and then click on C6 and Edit --> Paste Special and tick "Transpose" then the numbers will go down from C6. I'm not sure what you mean in the first question.

 

[pit29]

To answer your second question, you can use the "Transpose" box in paste special. If you copy a series of numbers in A1, B1, C1, D1, E1 and then click on C6 and Edit --> Paste Special and tick "Transpose" then the numbers will go down from C6. I'm not sure what you mean in the first question.

Thanks for your reply! For point 2, I am actually looking for a formula-based approach. I want C5 to be = A1, C6 = C1, C7 = E1 and so forth. So each cell below C5 references the same row, but two cells further to the right than the cell above it. This way, I can fill cells further down automatically, and their content would automatically be adjusted if I change something in A.

Sorry for not being clear in point 1. I have an Example:

. A B
1 Ta 15
2 Tb 12
3 Tc 20

So, I want a formula that looks in B1:B3 for the minimum value and then returns whatever is in the cell next to that value to the left. In this case, the result of the formula would be "Tb". Is that possible?

 

[James_C]

Thanks for your reply! For point 2, I am actually looking for a formula-based approach. I want C5 to be = A1, C6 = C1, C7 = E1 and so forth. So each cell below C5 references the same row, but two cells further to the right than the cell above it. This way, I can fill cells further down automatically, and their content would automatically be adjusted if I change something in A.

Sorry for not being clear in point 1. I have an Example:

. A B
1 Ta 15
2 Tb 12
3 Tc 20

So, I want a formula that looks in B1:B3 for the minimum value and then returns whatever is in the cell next to that value to the left. In this case, the result of the formula would be "Tb". Is that possible?

Yes there is a way to return the information you want, but it involves three formulas, which can be combined, but first lets do this step by step.

In Cell B5 enter a formula =MIN(B1:B3) this will return the minimum value in the list. Easy so far ...

Step 2 in Cell B6 enter the formula = MATCH(B5,B1:B3,0) This formula returns the relative row number of the range (B1:B5) of the value that you you are looking for (B5), the last variable in the formula (0) tells the formula to find an exact match. As the lowest value is second row in the range, the formula returns the value 2.

Step 3 now that we know the relative row number in the range, we can use the offset formula to pull the correct Row Label. In Cell B7 enter the formula =OFFSET(A1,B6-1,0). This formula allows you to return data in cell based on a reference cell (A1 in this case) and a number of rows down, and right from that reference cell. We need to take -1 off the value returned in Step 2, as the offset formula will take the value of 1 as one row down from the reference point. If you have entered the formula correctly it will return the value 'Tb'

Once you understand how the formula is calculating, you can combine the formula into a nested statement =OFFSET(A1,MATCH(MIN(B1:B5),B1:B5,0)-1,0)

The beauty of the formula is you could set the formula to look at range of all of column B ( provided the formula is is a different column ) and it will still work as you add data, by adding new rows

=OFFSET(A1,MATCH(MIN(B:B),B:B,0)-1,0)

The only thing that you need to be aware of is if there are two or more cells that contain the same minimum value the formula will only return the first match.

Hope this helps

 

[pit29]

Yes there is a way to return the information you want, but it involves three formulas, which can be combined, but first lets do this step by step.

In Cell B5 enter a formula =MIN(B1:B3) this will return the minimum value in the list. Easy so far ...

Step 2 in Cell B6 enter the formula = MATCH(B5,B1:B3,0) This formula returns the relative row number of the range (B1:B5) of the value that you you are looking for (B5), the last variable in the formula (0) tells the formula to find an exact match. As the lowest value is second row in the range, the formula returns the value 2.

Step 3 now that we know the relative row number in the range, we can use the offset formula to pull the correct Row Label. In Cell B7 enter the formula =OFFSET(A1,B6-1,0). This formula allows you to return data in cell based on a reference cell (A1 in this case) and a number of rows down, and right from that reference cell. We need to take -1 off the value returned in Step 2, as the offset formula will take the value of 1 as one row down from the reference point. If you have entered the formula correctly it will return the value 'Tb'

Once you understand how the formula is calculating, you can combine the formula into a nested statement =OFFSET(A1,MATCH(MIN(B1:B5),B1:B5,0)-1,0)

That makes sense. Thank you, James! I hope with some thinking I can use the =OFFSET thing to make my second question work as well.

 

[blodwyn]

Sorry for not being clear in point 1. I have an Example:

. A B
1 Ta 15
2 Tb 12
3 Tc 20

So, I want a formula that looks in B1:B3 for the minimum value and then returns whatever is in the cell next to that value to the left. In this case, the result of the formula would be "Tb". Is that possible?

In the example above, the normal way of doing this with a VLOOKUP would only work if Column B was in numerical ascending order. If sorting the order is not feasible then the solution is going to be more convoluted.

Edit: Great answer appeared by James while I was in the middle of posting

 

[pit29]

In the example above, the normal way of doing this with a VLOOKUP would only work if Column B was in numerical ascending order. If sorting the order is not feasible then the solution is going to be more convoluted.

Edit: Great answer appeared by James while I was in the middle of posting

Thank you as well, blodwyn. Sorting is not an option, unfortunately... it's really an area. But speaking of sorting, is there a formula to get an area sorted? E.g., can I have A1:B5 sorted (B ascending) and the result in D1:E5? That'd be cool...

 

[blodwyn]

Thank you as well, blodwyn. Sorting is not an option, unfortunately... it's really an area. But speaking of sorting, is there a formula to get an area sorted? E.g., can I have A1:B5 sorted (B ascending) and the result in D1:E5? That'd be cool...

Not that I'm aware of. I only know how to do it via the menu Data>Sort, or I guess a macro would also work, but I don't know of a formula that would sort a range into another range

 

[Doctor Q]

I have an Example:

. A B
1 Ta 15
2 Tb 12
3 Tc 20

So, I want a formula that looks in B1:B3 for the minimum value and then returns whatever is in the cell next to that value to the left. In this case, the result of the formula would be "Tb". Is that possible?

Here's one way. Set some cell to min(B1:B3). Let's assume it's B4. In C1, put this formula:

=IF(B1=B$4,A1,"")

and copy it down column C. The values in column A (there may be more than one) that have the minimums in column B will appear in the same rows of column C.

. A   B C 
1 Ta 15
2 Tb 12 Tb
3 Tc 20

You could also put

=IF(B1=min(B$1:B$3),A1,"")

in each cell, but that might take longer to compute by a split second.

 

[Doctor Q]

For point 2, I am actually looking for a formula-based approach. I want C5 to be = A1, C6 = C1, C7 = E1 and so forth. So each cell below C5 references the same row, but two cells further to the right than the cell above it. This way, I can fill cells further down automatically, and their content would automatically be adjusted if I change something in A.

My solution:

In C5 put this formula: =OFFSET(A$1,0,2*(ROW()-5))

Copy it down to C6, C7, etc.

It's the equivalent of putting these formulas in one by one:

C5: =OFFSET(A$1,0,2*0)
C6: =OFFSET(A$1,0,2*1)
C7: =OFFSET(A$1,0,2*2)
...

 

[pit29]

Thanks, Dr. Q! Your suggestion for the minimum issue was actually the way I always did that. I appreciate your suggestion for the other point! That'll make my life easier, definitively...