Display Nearest Rounded Sizes Calculated From Values In Textbox & Cells

Hey everyone, it's been a while but I have an issue I'm puzzling over. I'm trying to have Excel fill in cells for me which would calculate and list the nearest sizes, based on values entered into a text box on Sheet 1 (TB) and two values found in Columns B (X) and Column C (Z) on Sheet 2. Please see the attached sample workbook for more clarity.

Here is how the code needs to calculate what gets entered into Cell E8 on Sheet 2, for example:

If on Sheet 2 both Cells B8 and C8 contain numbers, then calculate the following - show no data otherwise:

The textbox value from Sheet 1 (TB) must be entered first, followed by '_X_', in our example: '1.5 X '

Both (X) and (Z) on Sheet 2 need to be evaluated to see which one is closer to a nearest increment of 0.5, rounded up; in case of a tie, the lower number should be chosen. (See Cell E7 on Sheet 2 in the sample worksheet.) In our example, (X)=1.8 and (Z)=2.1, which means 1.8's closest 0.5 increment is 2.0 (with a difference of 0.2) while 2.1's closest increment is 2.5 (with a difference of .4). Since (X) has the closer increment, its the one that gets chosen for the next part of the value displayed in Cell E8, followed by the text of '_BAR__L=', which now makes our example appear like this: '1.5 X 2.0 BAR L='

All that's left now is to take the remaining value, in our case (Z)=2.1, and add it to the end of what we've already established in our example for Cell E8, which would read: '1.5 X 2.0 BAR L=2.1'

I hope that makes sense, please let me know if something is unclear, and thank you so much in advance for taking the time to look into this.

Wow that was fast, thank you!

That is looking really good. I updated the textboxes and attached the latest version. The textbox value from Sheet 1 still isn't being displayed but you probably knew that.

I am a little stumped as to why tied values are not always being chosen correctly. If I enter 2.1 into the (X) column and 4.6 into the (Z), 2.1 is chosen correctly and the result is ' X 2.5 BAR L=4.6' which is as it should be. But if I enter 2.7 and 3.7, 3.7 is chosen for a result of ' X 4.0 BAR L=2.7' instead of 2.7 being chosen for a result of ' X 3.0 BAR L=3.7'. Any ideas?

As for the rest, just to clarify and hopefully prevent further issues, the final code needs to kick in for the appropriate Cell E6:E35 any time data is entered into its corresponding (X) or (Z) column cells, to see whether (TB) contains a value and both (X) and (Z) also contain values - and if so, then display the results. If all three do not contain a value, then the appropriate Cell E6:E35 should display the value from Cell F8 on Sheet 1.

Thanks again!

OK, will take a look later. The tie probably doesn't work because I missed that bit in your original post!

A further thought - perhaps the code would better be attached to a button? If it is triggered by a change to the worksheet it will do so after X is changed and then after Z is changed.

Thanks so much, I really appreciate that!

I also considered a button, and while I won't throw that idea out the window, it would be significantly more user-friendly in this case to have the code activate every time (X) or (Z) is changed. As long as that works! I should mention too that I edited my last post specifying another Cell value to display in the appropriate Cell E6:E35 if the conditions aren't met. Let me know if you have any issues though.

Awesome, this is really coming together! Thanks for pointing out the other textbox, I completely missed that. The rounding seems to be working great, apart from the odd persisting issue of when I use a value of 1.7 in (X) and 3.7 in (Z). (See Row 8 in the attached sample file.) The result is going with 4.0 instead of using the smaller value in case of a tie: 2.0. Weird.

My apologies for not being clear about the change to my earlier post - I edited it as you were writing a reply so I figured you missed it. Here is what I changed: 'If all three do not contain a value, then the appropriate Cell E6:E35 should display the value from Cell F8 on Sheet 1.' At the moment, deleting a value in either (X) or (Z) doesn't change the appropriate Cell E6:E35 value. Now that I’ve had a chance to test this out and see how this all works together though, it appears this might be more complicated than I first anticipated. This took a while to figure out, but I tried to be as clear as possible - here’s how it should all function:

Every time a cell in (X) or (Z) changes by either receiving a value or by having a value deleted, the code needs to do the following:

1. Check whether (F8) contains two separate numerical entities (in the format of 1.5 X 2.0, for example) and (TB) contains a value. If either one does not, clear the appropriate E6:E35 Cell and stop running the code. (No error messages should be displayed at any time.)

2. If (F8) and (TB) both contain values, check how many cells in the (X/Z) cell range that just changed contain values. If 0, clear the appropriate E6:E35 Cell and stop running the code.

3. If 1 cell in the (X/Z) cell range contains data, display the (F8) value in the appropriate E6:E35 Cell followed by ‘__L=’ followed by the data from that 1 cell in the (X/Z) range. (See Rows 13 & 14 in the attached sample file.)

4. If both cells in the (X/Z) cell range contain values, there is one extra thing we do need to add here first, and that is to check whether one or both of the values in these (X/Z) cells contain a value that is larger than the largest value displayed in (F8). If neither of them does, the code can run as usual, with the rounding up to the nearest 0.5 value, if a tie choose the lower number, and so forth.

5. If 1 value in the (X/Z) cell range is larger than the largest value displayed in (F8), that one value MUST be the one chosen to be displayed after the ‘L=’ in the appropriate E6:E35 Cell. (See Row 9 in the attached sample file.)

6. If both values in the (X/Z) cell range are larger than the largest value displayed in (F8), clear the appropriate E6:E35 Cell and stop running the code. (See Row 10 in the attached sample file.)

Phew, I really hope that is clear enough! I’m really sorry for not realizing the whole F8 thing until later and really hope it’s not too painful to incorporate that. Please yell if you have any questions whatsoever, and thank you!

Your guess is correct, but no worries StephenR, I realize my last post does add a lot of stuff and I appreciate that you're even willing to keep helping.

First off dude, you rock. That is just about perfect! I was only able to find one small issue, and that's regarding point number five that I mentioned earlier:

5. If 1 value in the (X/Z) cell range is larger than the largest value displayed in (F8), that one value MUST be the one chosen to be displayed after the ‘L=’ in the appropriate E6:E35 Cell. (See Row 9 in the attached sample file.)

Just to be clear - this means the other value from the (X/Z) range that is smaller than the largest value in (F8) needs to be rounded up to the nearest 0.5 increment and placed after the value from (TB) as usual.

This is working with certain values, but not with other values - I have no idea why. Here are three examples of what should happen, followed by three more examples of what actually happens. I'm using the 1.5 X 2.5 example for Cell F8:

1. X=3.2 Z=1.6 DESIRED RESULT=1.5 X 2.0 BAR L=3.2
2. X=2.1 Z=3.3 DESIRED RESULT=1.5 X 2.5 BAR L=3.3
3. X=1.6 Z=3.8 DESIRED RESULT=1.5 X 2.0 BAR L=3.8

1. X=3.2 Z=1.6 ACTUAL RESULT=1.5 X 3.5 BAR L=3.2
2. X=2.1 Z=3.3 ACTUAL RESULT=1.5 X 3.5 BAR L=3.3
3. X=1.6 Z=3.8 ACTUAL RESULT=1.5 X 4.0 BAR L=3.8

Any idea what's going on there?

PS One other small thing I noticed - deleting multiple cells at the same time doesn't clear the corresponding E Cell values. I don't know if that's a big issue for you to implement, but I thought I should run it by you and get your thoughts.

Thanks again! This is a huge help! :D

The rounding needs to be done in these two cases:

1. If neither number in the (X/Z) range is greater than the largest value in (F8), both numbers need to round up to the nearest 0.5 value.

2. If only 1 number in the (X/Z) range is greater than the largest value in (F8), the other number still needs to round up to the nearest 0.5 value. For example:

(F8)=1.5 X 2.5 BAR
(TB)=1.5

(X)=3.4
(Z)=2.1

(E)=1.5 X 2.5 BAR L=3.4

And of course if both (X/Z) values are greater than the largest value in (F8), the appropriate E cell is cleared and no rounding happens.

Does that help clarify a little?

Ah, my apologies, I see what you mean. You may have been on to something there, because after putting the code to use, we’re running into issues with minimum values. Basically, everything stays the same except for this:

If ONE of the (X/Z) values are LESS THAN the minimum value displayed in (F8), then the minimum value from (F8) needs to be used instead and MUST be displayed AFTER the value from (TB) in the corresponding (E) cell with the other value being displayed after the ‘L=’.

If BOTH of the (X/Z) values are smaller than the minimum value displayed in (F8), then the corresponding (E) cell needs to clear and the code stop running.

So taking the rules from earlier, 3 stays the same while 4-6 get the additions (in RED CAPS) for the minimum values:

3. If 1 cell in the (X/Z) cell range contains data, display the (F8) value in the appropriate E6:E35 Cell followed by ‘__L=’ followed by the data from that 1 cell in the (X/Z) range.

4. If both cells in the (X/Z) cell range contain values, there is one extra thing we do need to add here first, and that is to check whether one or both of the values in these (X/Z) cells contain a value that is larger OR SMALLER than the largest OR SMALLEST value displayed in (F8). If neither of them does, the code can run as usual, with the rounding up to the nearest 0.5 value, if a tie choose the lower number, and so forth.

5. If 1 value in the (X/Z) cell range is larger than the largest value displayed in (F8), that one value MUST be the one chosen to be displayed after the ‘L=’ in the appropriate E6:E35 Cell. IF 1 VALUE IN THE (X/Z) RANGE IS SMALLER THAN THE SMALLEST VALUE DISPLAYED IN (F8), THAT SMALLEST (F8) VALUE MUST BE CHOSEN TO BE DISPLAYED AFTER THE ‘(TB)_X_’ IN THE APPROPRIATE E6:E35 CELL.

6. If both values in the (X/Z) cell range are larger OR SMALLER than the largest OR SMALLEST value displayed in (F8), clear the appropriate E6:E35 Cell and stop running the code.

How difficult would that be to incorporate? And does it make sense? I had no idea this would be half this complex when I first started out, so if this is too much for you at this point I will totally understand and can start a new thread, please just let me know.

I did also notice one last thing - the data following the two numbers separated by the '_X_' in Cell F8 is displayed in the appropriate (E) cell only if 1 value is present in the (X/Z) range. Would we be able to have the same value display when there are two values in the (X/Z) cell range as well? So instead of putting the word 'BAR' in there, we always take whatever value is following the numbers in (F8)?

Everything else works perfectly! Thank you!!

Thank you so much for not giving up! I am sure we almost have it.

I attached another sample workbook so you can see what is happening. Everything seems to be working apart from when one of the two values in the (X/Z) cell range is smaller than the minimum value displayed in (F8). The smallest (F8) value needs to be displayed in the corresponding E range instead of that smaller number. Let me know if that's not clear.

Clarifying the whole BAR thing - I found out that the word 'BAR' will not always be used, so it would be better to take that out of the code and use the word that is entered into (F8) instead, for all cases. You can see examples in the sample worksheet as well.

I've made some changes but X=2.8 and Z=0.4 results in

1.5 X 0.5 PLATE L=2.8

not

1.5 X 1.5 PLATE L=2.8

as you indicated because the rule about one of the values exceeding the max of F8 is applied first. So, one value exceeds the max of F8 and one value is smaller than the min of F8. What do you want to happen in such cases? Apologies if you've already said but I don't fancy re-reading the whole thread!

No worries! In the case where one value exceeds the max of (F8) and the other is smaller than the minimum of (F8), I think both rules can still technically still apply - but maybe we do need a separate rule for this scenario?

If so, it should work like this:

If 1 value in (X/Z) range is greater than the maximum value in (F8) AND 1 value in (X/Z) range is smaller than the minimum value in (F8), then display the following: (TB)('_X_')(MINIMUM VALUE OF F8)(VALUE FOLLOWING NUMBERS IN F8)('_L=')(GREATER THAN MAXIMUM VALUE OF X/Z)

Basically, any time one value in (X/Z) is greater than the maximum in (F8), it must always be displayed after the 'L=', and any time one value in (X/Z) is smaller than the minimum in (F8), it must always be replaced with the minimum value in (F8) following the (TB) value, with the other value being displayed after the 'L='.

Clear as mud?

My head was spinning pretty badly trying to explain all the angles without missing anything, so I can only imagine the dizziness you're experiencing.

This is fortunately and also unfortunately the closest we have been. The wording transfer works perfectly, and all the minimum/maximum combinations work really well. But of course, the one example which I made a mistake on in the last attached workbook is the last one not working and, it seems, an exception to the rule:

(F8)= 1.5 X 2.5 BAR

(X)= .9
(Y)= 2.4

ACTUAL (E)= 1.5 X 1.5 BAR L=2.4
REQUIRED (E)= 1.5 X 2.5 BAR L=0.9

My rule description from my last post is still correct, but note that it only applies when BOTH one value is greater AND one value is smaller:

If 1 value in (X/Z) range is greater than the maximum value in (F8) AND 1 value in (X/Z) range is smaller than the minimum value in (F8), then display the following: (TB)('_X_')(MINIMUM VALUE OF F8)(VALUE FOLLOWING NUMBERS IN F8)('_L=')(GREATER THAN MAXIMUM VALUE OF X/Z)

The following statement wasn't entirely accurate:

Basically, any time one value in (X/Z) is greater than the maximum in (F8), it must always be displayed after the 'L=', and any time one value in (X/Z) is smaller than the minimum in (F8), it must always be replaced with the minimum value in (F8) following the (TB) value, with the other value being displayed after the 'L='.

The over-maximum rule is perfect, I said that any time one value in (X/Z) is greater than the maximum in (F8), it must always be displayed after the 'L='. (Whether the other value is smaller than minimum OR NOT.) This works great, no problem here at all.

The problem lies with the minimum rule description, where I claimed that any time one value in (X/Z) is smaller than the minimum in (F8), it must ALWAYS be replaced with the minimum value in (F8) following the (TB) value, with the other value being displayed after the 'L='. This IS NOT entirely true, as shown by the example above. There are these two cases that could occur when only one of the two (X/Z) values are under minimum size:

1. When the other value is over maximum, then yes, the under minimum value must work as it does now. This part works great.

2. If the the other value IS NOT over maximum though, then the under-minimum value must be compared with the second value to see which is closer to the nearest 0.5 value, as usual - however, when rounding, the nearest 0.5 value for the under-minimum value cannot be less than the minimum value from (F8).

So, all the other examples in the last attached workbook work extremely well with no issues. All we still need is for that last examples to work, and we've nailed it. I am really sorry I missed it. I even tried to edit your code to fix it myself, but to no avail. I attached the workbook again with a few more examples to hopefully keep your head spinning down to a minimum, even though mine is off again. Are you still with me?

That was quick. And ALMOST! (I am really starting to not like that word.) This example (Row 16) is the only one on the sheet not doing what it's supposed to. It was working before the most recent code change:

(F8)= 1.5 X 2.5 PLATE

(X)= 1.3
(Y)= 2.3

ACTUAL (E)= 1.5 X 2.5 PLATE L=1.3
REQUIRED (E)= 1.5 X 1.5 PLATE L=2.3

If the under-minimum value is closer to its nearest allowed 0.5 increment than the other value (or if there's a tie as in the example), the lesser number still needs to be chosen and its nearest allowed 0.5 increment used after the '_X_'. I hope that's just a simple tweak.

Absolutely StephenR. Anything I can do to make this as clear as possible.

Here is the legend:

F8= Cell F8 on Sheet 1
TB= Textbox on Sheet 1
X= A cell in Column B6:B35 on Sheet 2
Z= A cell in Column C6:C35 on Sheet 2
E= A corresponding cell in Column E6:C35 on Sheet 2
X/Y= A cell range consisting of 2 consecutive cells on Sheet 2
DFV= The data following the numerical values in F8
VO= A value from X/Z over the max value from F8
VU= A value from X/Z under the min value from F8
VB= A value from X/Z between the max and min value from F8
VUR= A value from X/Z under the min value from F8, rounded up to the min value from F8
VBR= A value from X/Z between the max and min value from F8, rounded up to the nearest 0.5 increment

And here are the rules:

Every time X or Z changes by either receiving a value or by having a value deleted, the code needs to do the following:


A. Check whether F8 contains two separate numerical entities and if TB contains a value. If either one does not, clear E and stop running the code; otherwise go to step B.


B. If F8 and TB both contain values, check how many cells in X/Z that just changed contain values. If 0, clear E and stop running the code; if 1 go to step B1; if 2 go to step C.

B1. If 1 cell in X/Z contains data, no matter what the number, display the F8 value in E followed by ‘DFV_L=’ followed by the data from that 1 cell in X/Z, and stop running the code.


C. If both cells in X/Z contain data, determine which 1 of the following 6 possible conditions are now met:

C1. 1 value over max, 1 value between max and min (VO & VB)
C2. 2 values over max (VO & VO)
C3. 1 value over max, 1 value under min (VO & VU)
C4. 1 value under min, 1 value between max and min (VU & VB)
C5. 2 values under min (VU & VU)
C6. 2 values between max and min (VB & VB)

If C1, then go to step D1.
If C2, then go to step D2.
If C3, then go to step D3.
If C4, then go to step D4.
If C5, then go to step D2.
If C6, then go to step D5.


D. These are the final 5 possibilities for the data displayed in E, based on the condition as determined by C.

D1. (TB)(_X_)(VBR)(DFV_L=)(VO)

D2. Clear E and stop running the code.

D3. (TB)(_X_)(VUR)(DFV_L=)(VO)

D4. Compare differences of both VU to VUR and VB to VBR; choose value with least difference (lower value in case of a tie) and display its legal rounded up value after the (_X_): (TB)(_X_)(value with least difference's rounded up value)(DFV_L=)(other value)

D5. Compare differences of both VB values to their respective VBRs; choose VB with least difference (lower value in case of a tie) and display its rounded up value after the (_X_): (TB)(_X_)(value with least difference's rounded up value)(DFV_L=)(other value)


That, I do believe, is everything that we needed to clarify. If anything is unclear or doesn't compute, you may feel free to yell at me. Thank you again, StephenR.

Dude. That is fantastic. Thank you so much! I really, really appreciate all your efforts. We are done!