Which formula could perform this task ?

blitssman

New Member
Joined
Aug 5, 2019
Messages
10
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!!:LOL:

Thank you All in advance!!

real value order
rounded value from list
875 X 1150
900 X 1200
1478 X 3620
1500 X 3600




List below


<tbody>
</tbody>

900 X 300
1200 X 300
1500 X 300
1800 X 300
2400 X 300
900 X 600
1200 X 600
1500 X 600
1800 X 600
2400 X 600
900 X 900
1200 X 900
1500 X 900
1800 X 900
2400 X 900
900 X 1200
1200 X 1200
1500 X 1200
1800 X 1200
2400 X 1200
900 X 1500
1200 X 1500
1500 X 1500
1800 X 1500
2400 X 1500
900 X 1800
1200 X 1800
1500 X 1800
1800 X 1800
2400 X 1800
900 X 2100
1200 X 2100
1500 X 2100
1800 X 2100
2400 X 2100
900 X 2400
1200 X 2400
1500 X 2400
1800 X 2400
2400 X 2400
900 X 2700
1200 X 2700
1500 X 2700
1800 X 2700
2400 X 2700
900 X 3000
1200 X 3000
1500 X 3000
1800 X 3000
2400 X 3000
900 X 3300
1200 X 3300
1500 X 3300
1800 X 3300
2400 X 3300
900 X 3600
1200 X 3600
1500 X 3600
1800 X 3600
2400 X 3600
900 X 3900
1200 X 3900
1500 X 3900
1800 X 3900
2400 X 3900
900 X 4200
1200 X 4200
1500 X 4200
1800 X 4200
2400 X 4200

<tbody>
</tbody>
 
G'day Blitssman,

My apologies for the delayed response. Copy and paste of the content below (note that the formula behind B1 will need to be entered in manually if you cut and paste this into a workbook).

The formula I gave goes in B1, the two sizes are entered individually in A1 and A2 with the result displayed in B1 as the output of the formula.
E & F are the look-up tables for the formula in B1. E is where the A1 data will be looked up, F is where the A2 data is looked up.

Sorry its not a bit more visual, but I haven't worked out that capability yet.

Cheers

shane

.............A.....|.................B....................|..|..|..............E...|............F
250 900 X 420024004200
415018003900
15003600
12003300
9003000
02700
2400
2100
1800
1500
1200
900
600
300
0

<tbody>
</tbody>
 
Last edited:
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Actually, if your "List below" is unchanging then you could try this standard-entry formula.

Lookup Rounded Up (2)

ABCDE
1real value orderrounded value from list
2875 X 1150900 X 1200
31478 X 36201500 X 3900
42400 X 42002400 X 4200
5
6
7900 X 3001200 X 3001500 X 3001800 X 3002400 X 300
8900 X 6001200 X 6001500 X 6001800 X 6002400 X 600
9900 X 9001200 X 9001500 X 9001800 X 9002400 X 900
10900 X 12001200 X 12001500 X 12001800 X 12002400 X 1200
11900 X 15001200 X 15001500 X 15001800 X 15002400 X 1500
12900 X 18001200 X 18001500 X 18001800 X 18002400 X 1800
13900 X 21001200 X 21001500 X 21001800 X 21002400 X 2100
14900 X 24001200 X 24001500 X 24001800 X 24002400 X 2400
15900 X 27001200 X 27001500 X 27001800 X 27002400 X 2700
16900 X 30001200 X 30001500 X 30001800 X 30002400 X 3000
17900 X 33001200 X 33001500 X 33001800 X 33002400 X 3300
18900 X 36001200 X 36001500 X 36001800 X 36002400 X 3600
19900 X 39001200 X 39001500 X 39001800 X 39002400 X 3900
20900 X 42001200 X 42001500 X 42001800 X 42002400 X 4200

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:107px;"><col style="width:151px;"><col style="width:90px;"><col style="width:90px;"><col style="width:90px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B2=LOOKUP(LEFT(A2,FIND(" ",A2))+0,{1,901,1201,1501,1801},{900,1200,1500,1800,2400})&" X "&CEILING(REPLACE(A2,1,FIND("X",A2),"")+0,300)

<tbody>
</tbody>

<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4


Hello Pete,

I"ve trieed this formula and its not working or it maybe just me.Did it work for you? . I've used (;) in between each formulas entry + did not leave any space was pretty much like in the forum . i've tried multiple different ways to make it work!! unfortunately for now i am on a process of learning by doing error i guess!!. i wonder what i am doing wrong. in this formula above, where do excel find its range (list) or where do i entered the list range in the formula?

thank you for your patience and your precious time.:)
 
Upvote 0
HEYYYYYYYY PETE!!! it workkkkkkkkkkkkkkssss hourrrraaaa !!!!:):pray:

after numerous attemps i finally got it right!!! hehehheheeh i am super happy!!!

you guys are awsome!! big thumbs UP to this forum!!! i am glad i found you guys! god bless you all!!! Christian
 
Upvote 0
hello shane!!!:)


heyy thank you for your time spent on publishing on my post triing to help me out!! but today i got it sort out with the help of Pete!!!
All help (sugestion) were all great ideas!!! thank you
thanks for your precious time!!! i will be back for sure in here soon or later !!! take care!!!
 
Upvote 0
G'day Blitssman,

You're welcome, and thanks for your kind words. I enjoy the opportunites to assist, as it challenges me to find solutions to problems I may not normally come across.

Cheers

shane
 
Upvote 0
HEYYYYYYYY PETE!!! it workkkkkkkkkkkkkkssss hourrrraaaa !!!!:):pray:

after numerous attemps i finally got it right!!! hehehheheeh i am super happy!!!

you guys are awsome!! big thumbs UP to this forum!!! i am glad i found you guys! god bless you all!!! Christian
You are very welcome! Glad you figured it out. :)

I agree with Shane:
I enjoy the opportunites to assist, as it challenges me to find solutions to problems I may not normally come across.

Cheers

shane

in this formula above, where do excel find its range (list) or where do i entered the list range in the formula?
I guess you figured out that the formula does not use the actual list in the sheets but the critical values are entered directly in the formula since my formula suggestion was prefaced with my comment ".. if your "List below" is unchanging
.."
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top