Rounding to a list

[pdgood]

Is there a way to round to the next highest number in a list?
Also, to the next lowest number in a list?

The list is a bit irregular. It starts off 1,2,3....etc but then goes 7,10,15,20...and other irregular intervals.

The list will be in Column B.

I'd like it to tell me not only the rounded number, but what cell it's in so I can use that in another formula.

Sorry, if this is unclear, let me know and I'll explain deeper.

Thanks!

[DonkeyOte]

If you list the 25 values in ascending order you can use something like the below to do both:

Please Login or Register  to view this content.

where A1 holds original value and Z1:Z25 the appropriate final values.

(assumes if match use match)

[MarvinP]

You've tried:
Round, RoundUp, RoundDown, Large, Small and Rank!
Do you know the numbers that are in the list or do they change?
What should happen at the highest number if you want the one higher? Lowest and lower?

[pdgood]

The numbers in the list will always be the same - they are target values, so to speak.

I've just been informed that there are 100 of them instead of 25 (doing this to help a co-worker, so information is trickling in).

Good question about the extremes - 1% and 100%. We are scanning dot percentages in film. I believe the numbers we scan will always be higher so that means 1% is not a concern. Film can not go higher than 100% so that's not a concern either. (And in truth, that end of the spectrum is not critical.)

[ChemistB]

Did you try Donkey's solution?

[pdgood]

Yes, and thanks!!! But I think I edited my question while he was working on the solution. What he wrote DID work - it returned the rounded up and down numbers. Now I need it to return what cells those numbers are in so that I can use that in another formula.

Here's an example of the big picture of what I'm trying to get to. This can be done in more than one formula obviously.

We measure an original dot percentage value - let's take 25% as our example. It's on a list in Column A and due to our process we're hoping it will measure 42% on our list in column B. But instead it reads 31%. So now I look to see what did actually read close to 42% and I find a 44 (rounded up to the nearest number in column B) and a 35 (rounded down). Now I need to reference those back to their original dot percentages (Column A) which turn out to be 44=40 and 35 =30. That tells me that my answer is going to be around 38% (Since 42 is closer to 44).

This is one of those deals where the worker is just trying to have Excel automate a repetitive process. Even if it can't do the whole thing, every step is a huge help.

Let me know if a better explanation is needed.

[DonkeyOte]

Perhaps you could post an example / sample file so we can better understand / visualise the workflow.

I confess I'm rarely a fan of returning the "address" of a cell as it's rarely warranted and invariably requires use of Volatile INDIRECT
there's often [not always] an INDEX based alternative.

[MarvinP]

Hi pjgood,

Wow your co-worker with a User Defined Function called Closest.

It takes a Range and a number as arguments. It will return the number in the range that is closest to the number.

See attached.

[pdgood]

You're right. And my apologies for presenting this so poorly. I'm getting a better understanding of what is needed as I go. Let's start fresh and I'll attach a file.

We are in the graphics field and we are measuring the dot percentages in halftone (gray scale) images. We currently do this manually and hope that Excel will help automate some of these steps.

On the file attached: Using 25% as an example the answer should be 38%. Here's why.

When we scan a dot percent of 25% it should actually read 42% because our process manipulates the file. But in this case, it didn't read 42%, instead our measurement read 31%. This means we're off and for reasons that have not been explained to me, we can't just subtract the difference.

What we have to do next is look at which of our measurements actually is achieving something close to 42%. Unfortunately, as you can see it's not a complete list - we only measure a few items, so we're just trying to get as close as we can based on that data.

We can see that when we scanned a 40% image we achieved a measurement of 44%. That's pretty close. Also, when we scanned a 30% image we achieved a measurement of 35%. So that tells us the answer is in between those two but closer to the 40 that achieved 44. The correct answer then would be around 38%.

As you can see, it's an estimate at best. As I said, we're just trying to use Excel where we can to automate as much of this process as is possible. Every step saved helps because this is a time consuming process that is repeated over and over.

Thanks for your patience.

[DonkeyOte]

If I've understood I would suggest that you retrieve the Measurements in two cells and the % in a further two cells - then interpolate.

Of course where there is an exact match then the above is needless (no need to calculate the surrounding values).

Using your sample...

Please Login or Register  to view this content.

We will put the interpolated result in G2

Please Login or Register  to view this content.

there are no doubt better alternatives using other functions...

[pdgood]

Thanks for the well thought out reply. I'm not sure where I went wrong. Please see the attached file.
Also, I'm on a Mac so I assume I enter by Command-Shift-Enter rather than control. I tried both ways.

[DonkeyOte]

I'm not sure where I went wrong.

You used different cells to those in the suggestion (eg 42 in E2 rather than E1)
As such you must adjust the references accordingly (else use those detailed in the example).

Once corrected the result you will get is 37.78 (pending format applied)

[pdgood]

I'm soooo close, but not quite there.
I moved the formulas and they all achieve a result except the last one. See attached.

And thanks for your patience!!!

[DonkeyOte]

Copy F2 to F3 - presently the formulae are identical which they should not be.
When you copy F2 to the F3 cell the relative references will update (ie refs. to $E2 become $E3)

[pdgood]

Aaaaah perfect!!!!!!!!!!!!
Thanks!