Which formula could perform this task ?

blitssman · Aug 8, 2019

Hi everyone!!

Down below I have a list and the problem is this: I want to generate items from the list when a precise value like: 875 X 1150 or 1478 X 3620 is entered in a cell . Let say, someone has entered : 875 X 1150 or 1478 X 3620 in (column A) and I want in (column B) the rounded value from the list to show ! Is it possible to do this with excel? or need the VBA? Or what is my best option (tools) to get result like this? Ohhh by the way I am using excel 2007 and I am brand new at it!hehehe!!

Thank you All in advance!!

[TABLE="width: 323"]
<tbody>[TR]
[TD]real value order
[/TD]
[TD]rounded value from list
[/TD]
[/TR]
[TR]
[TD]875 X 1150
[/TD]
[TD] 900 X 1200
[/TD]
[/TR]
[TR]
[TD]1478 X 3620
[/TD]
[TD] 1500 X 3600
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD] List below
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 673"]
<tbody>[TR]
[TD]900 X 300
[/TD]
[TD]1200 X 300
[/TD]
[TD]1500 X 300
[/TD]
[TD]1800 X 300
[/TD]
[TD]2400 X 300
[/TD]
[/TR]
[TR]
[TD]900 X 600
[/TD]
[TD]1200 X 600
[/TD]
[TD]1500 X 600
[/TD]
[TD]1800 X 600
[/TD]
[TD]2400 X 600
[/TD]
[/TR]
[TR]
[TD]900 X 900
[/TD]
[TD]1200 X 900
[/TD]
[TD]1500 X 900
[/TD]
[TD]1800 X 900
[/TD]
[TD]2400 X 900
[/TD]
[/TR]
[TR]
[TD]900 X 1200
[/TD]
[TD]1200 X 1200
[/TD]
[TD]1500 X 1200
[/TD]
[TD]1800 X 1200
[/TD]
[TD]2400 X 1200
[/TD]
[/TR]
[TR]
[TD]900 X 1500
[/TD]
[TD]1200 X 1500
[/TD]
[TD]1500 X 1500
[/TD]
[TD]1800 X 1500
[/TD]
[TD]2400 X 1500
[/TD]
[/TR]
[TR]
[TD]900 X 1800
[/TD]
[TD]1200 X 1800
[/TD]
[TD]1500 X 1800
[/TD]
[TD]1800 X 1800
[/TD]
[TD]2400 X 1800
[/TD]
[/TR]
[TR]
[TD]900 X 2100
[/TD]
[TD]1200 X 2100
[/TD]
[TD]1500 X 2100
[/TD]
[TD]1800 X 2100
[/TD]
[TD]2400 X 2100
[/TD]
[/TR]
[TR]
[TD]900 X 2400
[/TD]
[TD]1200 X 2400
[/TD]
[TD]1500 X 2400
[/TD]
[TD]1800 X 2400
[/TD]
[TD]2400 X 2400
[/TD]
[/TR]
[TR]
[TD]900 X 2700
[/TD]
[TD]1200 X 2700
[/TD]
[TD]1500 X 2700
[/TD]
[TD]1800 X 2700
[/TD]
[TD]2400 X 2700
[/TD]
[/TR]
[TR]
[TD]900 X 3000
[/TD]
[TD]1200 X 3000
[/TD]
[TD]1500 X 3000
[/TD]
[TD]1800 X 3000
[/TD]
[TD]2400 X 3000
[/TD]
[/TR]
[TR]
[TD]900 X 3300
[/TD]
[TD]1200 X 3300
[/TD]
[TD]1500 X 3300
[/TD]
[TD]1800 X 3300
[/TD]
[TD]2400 X 3300
[/TD]
[/TR]
[TR]
[TD]900 X 3600
[/TD]
[TD]1200 X 3600
[/TD]
[TD]1500 X 3600
[/TD]
[TD]1800 X 3600
[/TD]
[TD]2400 X 3600
[/TD]
[/TR]
[TR]
[TD]900 X 3900
[/TD]
[TD]1200 X 3900
[/TD]
[TD]1500 X 3900
[/TD]
[TD]1800 X 3900
[/TD]
[TD]2400 X 3900
[/TD]
[/TR]
[TR]
[TD]900 X 4200
[/TD]
[TD]1200 X 4200
[/TD]
[TD]1500 X 4200
[/TD]
[TD]1800 X 4200
[/TD]
[TD]2400 X 4200
[/TD]
[/TR]
</tbody>[/TABLE]

kweaver · Aug 8, 2019

Welcome to the lounge.

I kinda brute forced my way through this with a macro. See what you think.

Code:

Sub Rounding()
Dim LR As Long, ans As String, loc As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LR
loc = InStr(Range("A" & i), "x")  ' find the times sign
n1 = 1 * Left(Range("A" & i), loc - 1)  ' turn the first entry into a number
n2 = Fix(Application.WorksheetFunction.Log(n1))  ' dump the decimal places; probably don't need this (was work in progress...lol)
n3 = 100 * Round(n1 / 100, 0)  ' round
ans = n3 & " x "  ' affix a times sign
n1 = 1 * Right(Range("A" & i), Len(Range("A" & i)) - loc)  ' get the 2nd number
n2 = Fix(Application.WorksheetFunction.Log(n1))   ' don't need
n3 = 100 * Round(n1 / 100, 0)
ans = ans & n3
Cells(i, 2) = ans  ' put result in B column
Next i
End Sub

kweaver · Aug 8, 2019

Here's a formula for it rather than the VBA;

Excel 2010

A

B

C

1

875 x 1150

900 x 1200

2

1478 x 3620

1500 x 3600

3

780 x 5300

800 x 5300

4

780 x 5800

800 x 5800

5

780 x 5890

800 x 5900

6

2410 x 4275

2400 x 4300

Sheet1

Cell Formulas
Range	Formula
C1	=(100ROUND(1LEFT(A1,1FIND("x",A1)-1)/100,0))&" x "&(100ROUND(1*RIGHT(A1,LEN(A1)-FIND("x",A1))/100,0))

DSCfromCFA · Aug 8, 2019

G'day Blitssman,

There is not a single formula that will do this easily, but it can be done if you change the way your data is presented and the way you enter the odd sizes.

With your example, a value such as 1200 X 2400 is actually text, not numbers (Excel autimatically left justifies text and right justifies numbers in a cell, so even one non-numeric character in a cell means it is all treated as text)

If you were to enter your sizes as numbers in two cells i.e A1 and A2 then we can do the rest fairly easily without the large table of standard sizes.

If you put your first range of sizes in column E starting at 2400 in E1 down to 0 in E6 and your second range in column F 4200 in F1 down to 0 in F15 then the following formula in B2 will give the result you want

Note this requires that your 0-2400 goes in A1 and 0-4200 goes in A2

=INDEX(E1:E6,MATCH(A1,E1:E6,-1),1)&" X "&INDEX(F1:F15,MATCH(A2,F1:F15,-1),1)

There may be better ways to do this, but this should work.

cheers

shane

blitssman · Aug 11, 2019

Heyyy shane,

Thanks for your input, unfortunatly i don't get it yet ,but it make me think doing things different!! I will try something!
Whenever, could you make a little excel sheet exemple of your thought on this... i am more of a visual type of person than... Have a good day!

blitssman · Aug 11, 2019

heyy kweaver!!

Thanks for your input. will let you know soon if it does the trick!! thanks & have a good day!!

kweaver · Aug 11, 2019

If you aren't forced to keep the data as text and could break up the multiplication into 2 columns, this would be the simplest solution:

Excel 2010

A

B

C

D

1

875

1150

900

1200

2

1458

3620

1500

3600

3

780

5300

800

5300

4

780

5800

800

5800

5

780

5890

800

5900

6

2410

4275

2400

4300

Sheet2

Cell Formulas
Range	Formula
C1	=ROUND(A1,-2)
D1	=ROUND(B1,-2)

Peter_SSs · Aug 12, 2019

Given the uniform structure of your main table, you could try this. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook

A

B

C

D

E

1

real value order

rounded value from list

2

875 X 1150

900 X 1200

3

1478 X 3620

1500 X 3900

4

2400 X 4200

5

6

7

900 X 300

1200 X 300

1500 X 300

1800 X 300

2400 X 300

8

900 X 600

1200 X 600

1500 X 600

1800 X 600

2400 X 600

9

900 X 900

1200 X 900

1500 X 900

1800 X 900

2400 X 900

10

900 X 1200

1200 X 1200

1500 X 1200

1800 X 1200

2400 X 1200

11

900 X 1500

1200 X 1500

1500 X 1500

1800 X 1500

2400 X 1500

12

900 X 1800

1200 X 1800

1500 X 1800

1800 X 1800

2400 X 1800

13

900 X 2100

1200 X 2100

1500 X 2100

1800 X 2100

2400 X 2100

14

900 X 2400

1200 X 2400

1500 X 2400

1800 X 2400

2400 X 2400

15

900 X 2700

1200 X 2700

1500 X 2700

1800 X 2700

2400 X 2700

16

900 X 3000

1200 X 3000

1500 X 3000

1800 X 3000

2400 X 3000

17

900 X 3300

1200 X 3300

1500 X 3300

1800 X 3300

2400 X 3300

18

900 X 3600

1200 X 3600

1500 X 3600

1800 X 3600

2400 X 3600

19

900 X 3900

1200 X 3900

1500 X 3900

1800 X 3900

2400 X 3900

20

900 X 4200

1200 X 4200

1500 X 4200

1800 X 4200

2400 X 4200

Lookup Rounded Up

Peter_SSs · Aug 12, 2019

Actually, if your "List below" is unchanging then you could try this standard-entry formula.

Excel Workbook

A

B

C

D

E

1

real value order

rounded value from list

2

875 X 1150

900 X 1200

3

1478 X 3620

1500 X 3900

4

2400 X 4200

5

6

7

900 X 300

1200 X 300

1500 X 300

1800 X 300

2400 X 300

8

900 X 600

1200 X 600

1500 X 600

1800 X 600

2400 X 600

9

900 X 900

1200 X 900

1500 X 900

1800 X 900

2400 X 900

10

900 X 1200

1200 X 1200

1500 X 1200

1800 X 1200

2400 X 1200

11

900 X 1500

1200 X 1500

1500 X 1500

1800 X 1500

2400 X 1500

12

900 X 1800

1200 X 1800

1500 X 1800

1800 X 1800

2400 X 1800

13

900 X 2100

1200 X 2100

1500 X 2100

1800 X 2100

2400 X 2100

14

900 X 2400

1200 X 2400

1500 X 2400

1800 X 2400

2400 X 2400

15

900 X 2700

1200 X 2700

1500 X 2700

1800 X 2700

2400 X 2700

16

900 X 3000

1200 X 3000

1500 X 3000

1800 X 3000

2400 X 3000

17

900 X 3300

1200 X 3300

1500 X 3300

1800 X 3300

2400 X 3300

18

900 X 3600

1200 X 3600

1500 X 3600

1800 X 3600

2400 X 3600

19

900 X 3900

1200 X 3900

1500 X 3900

1800 X 3900

2400 X 3900

20

900 X 4200

1200 X 4200

1500 X 4200

1800 X 4200

2400 X 4200

Lookup Rounded Up (2)

blitssman · Aug 12, 2019

Hello kweaver!

yeahhhhh!!!

it works !!! thanks But is it possible to make (round 300 %) see in list below
this list is growing by 300 millimeters which is 1 foot if i could get those ( 400 +, 500+ to fit in the 600 mm list and those 700+, 800+, would go in the 900 list ) hope you follow me!! hehehehe

it would be great i guess there is a bit of other formula to put in there?
i think substitute or replace could be a step formula to use .... has for now i can manage to make it work like this!!! thank you a lot!!! until next time!!!

[TABLE="width: 342"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]900 X 600[/TD]
[TD]1200 X 600[/TD]
[TD]1500 X 600[/TD]
[/TR]
[TR]
[TD]900 X 900[/TD]
[TD]1200 X 900[/TD]
[TD]1500 X 900[/TD]
[/TR]
[TR]
[TD]900 X 1200[/TD]
[TD]1200 X 1200[/TD]
[TD]1500 X 1200[/TD]
[/TR]
[TR]
[TD]900 X 1500[/TD]
[TD]1200 X 1500[/TD]
[TD]1500 X 1500[/TD]
[/TR]
[TR]
[TD]900 X 1800[/TD]
[TD]1200 X 1800[/TD]
[TD]1500 X 1800[/TD]
[/TR]
[TR]
[TD]900 X 2100[/TD]
[TD]1200 X 2100[/TD]
[TD]1500 X 2100[/TD]
[/TR]
[TR]
[TD]900 X 2400[/TD]
[TD]1200 X 2400[/TD]
[TD]1500 X 2400[/TD]
[/TR]
[TR]
[TD]900 X 2700[/TD]
[TD]1200 X 2700[/TD]
[TD]1500 X 2700[/TD]
[/TR]
[TR]
[TD]900 X 3000[/TD]
[TD]1200 X 3000[/TD]
[TD]1500 X 3000[/TD]
[/TR]
[TR]
[TD]900 X 3300[/TD]
[TD]1200 X 3300[/TD]
[TD]1500 X 3300[/TD]
[/TR]
[TR]
[TD]900 X 3600[/TD]
[TD]1200 X 3600[/TD]
[TD]1500 X 3600[/TD]
[/TR]
[TR]
[TD]900 X 3900[/TD]
[TD]1200 X 3900[/TD]
[TD]1500 X 3900[/TD]
[/TR]
[TR]
[TD]900 X 4200[/TD]
[TD]1200 X 4200[/TD]
[TD]1500 X 4200[/TD]
[/TR]
</tbody>[/TABLE]

Which formula could perform this task ?

blitssman

New Member

Excel Facts

kweaver

Well-known Member

kweaver

Well-known Member

DSCfromCFA

Board Regular

blitssman

New Member

blitssman

New Member

kweaver

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

blitssman

New Member

Similar threads

Forum statistics

Share this page

Which formula could perform this task ?

New Member

Excel Facts

Well-known Member

Well-known Member

Board Regular

New Member

New Member

Well-known Member

MrExcel MVP, Moderator

MrExcel MVP, Moderator

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock