match, vlookup and bar-charts in Excel (2007 or 2003) 2

[lionelhill]

Two questions for which I'd be grateful for help:

(1) I have a set of measurements (e.g. 306.2) which I'd like to look up in a table of previous results, and report a corresponding text (305=A, 306.1=B, 308=C...). I'd like the nearest match rather than the largest that doesn't overshoot, or the smallest that does overshoot. Because the measurements are non-integer numbers, the chance of an exact match is very small.

Is there a way to do this? Neither Match nor Vlookup seems to offer "nearest fit".

At the moment I'm handling this by creating a new column containing, for "B", the average of A's value and B's (etc.), and using Vlookup on this column instead of the true values. This works, but seems a bit inelegant and it's hard to explain the logic to my clients.

(2) Is there a way to do a bar-chart with a numerical x-axis in Excel? I.e. my data points happened corresponding to conditions 1, 2, 5.3, 8, 10, and I'd like them plotted at those intervals on the x-axis, not evenly spaced.

Many thanks in advance for any suggestions!

 

[SkipVought]

Hi,

I can't find a way to make the x axis, NOT a Category axis, for a Bar Chart.

You could sort of 'fool' it by structuring your source data to force the location of your bars, but I can't find how to lable the values on the axis
[tt]
x-axis
1 22
2 33


5.3 66


8 44

10 55
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Not overly clever but you could use a UDF like:

Function Get_Nearest_Match(dblVal As Double)

Dim c As Range

Dim dblCloseMatch As Double

Dim strMatchLetter As String

dblCloseMatch = 0


    For Each c In Range("sr_Values")

        If Abs(dblVal - c) < Abs(dblVal - dblCloseMatch) Then

            dblCloseMatch = c
            
            strMatchLetter = c.Offset(0, 1)
            
        End If

    Next

Get_Nearest_Match = strMatchLetter

End Function

where sr_Values is a list of the numbers that you have had previously with the corresponding letters to the right of them

Would probably be ok with a relatively small list of numbers and letters but if that list is large it will cause a big calc overhead



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

I did get the 'labels' to display on the vertical axis, using the Select Data icon in 2007 and including the x & y values in the range.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

As an alternative...this array formula I think works:

{=VLOOKUP(MAX(IF(ISNA(VLOOKUP(E1-MIN(ABS(E1-sr_Values)),sr_Values,1,FALSE)),0,VLOOKUP(E1-MIN(ABS(E1-sr_Values)),sr_Values,1,FALSE)),IF(ISNA(VLOOKUP(E1+MIN(ABS(E1-sr_Values)),sr_Values,1,FALSE)),0,VLOOKUP(E1+MIN(ABS(E1-sr_Values)),sr_Values,1,FALSE))),sr_ValuesLetter,2,FALSE)}

Don;t physically put the { } in there but use CTRL+SHIFT+RETURN to enter the formula to make it an array formula

sr_Values in this case is the same range as before whilst sr_ValuesLetter is a 2 column range containing all your prior numbers and their corresponding letters

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[lionelhill]

Skip, many thanks, the vertical axis approach should do for a quick look-see plot, and for a proper presentation plot I suppose I'll have to export to 3rd party graph software. I'd been hunting in Excel with no success, and if the experts here can't find a feature either, it probably doesn't exist. You've saved me a lot of time with further searching, which I appreciate.

Geoff xlbo, Strewth! Yes, the formula works, but you are clearly a gentleman with more grey matter between your ears than average. I am going to wrap a damp towel round my head and see if I can work out why it works. In the meantime, stars for all.

 

[xlbo]

Lionel - easiest way is to break it out into component parts - that's how I built the formula in the 1st place

The key to it are the array components:

E1-MIN(ABS(E1-sr_Values))

and

E1+MIN(ABS(E1-sr_Values))

The array formula MIN(ABS(E1-sr_Values)) gives the smallest difference between a value in E1 and the range of numbers in sr_Values (ABS means it will look only at the difference in the numbers not whether they are larger or smaller)

E1 + and E1 - then give the 2 possible values which could be found e.g. if E1 is 3.2 and MIN(ABS(E1-sr_Values)) is 0.2 then the 2 numbers to check for are 3.4 and 3

We then try a lookup into the range using those 2 values and setting the result to 0 for the one that is not found

We then take the MAX of the 2 values (one of which will be 0 (as a result of not being found by vlookup) and one of which will be the found value in the list) and lookup that value in the sr_ValuesLetter range to return the letter associated with that value

That formula started off as about 6 seperate formulae which I then combined once I was happy that the logic worked...

Not sure I'd fancy running that formula on more than a thousand or so records though....it's pretty calculation intensive!!


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[lionelhill]

Geoff, thanks, while you were typing, I was Thinking flat-out, and sorted out how you approached it. It's neat, and definitely worth that star. Since I only need to apply it to a few hundred lines, it could be quite workable. I'm impressed.

 

[xlbo]

No probs - happy I could help!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question