Which formula could perform this task ?

B

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!!:laugh:

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]
 
Sort by date Sort by votes
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
[TABLE="width: 505"]
<tbody>[TR]
[TD="align: right"]250[/TD]
[TD] 900 X 4200[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2400[/TD]
[TD="align: right"]4200[/TD]
[/TR]
[TR]
[TD="align: right"]4150[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"]3900[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]3600[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]3300[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]3000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2700[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2400[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1800[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1200[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]900[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]600[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Peter_SSs said:
Actually, if your "List below" is unchanging then you could try this standard-entry formula.

Lookup Rounded Up (2)

ABCDE
real value orderrounded value from list
875 X 1150900 X 1200
1478 X 36201500 X 3900
2400 X 42002400 X 4200
900 X 3001200 X 3001500 X 3001800 X 3002400 X 300
900 X 6001200 X 6001500 X 6001800 X 6002400 X 600
900 X 9001200 X 9001500 X 9001800 X 9002400 X 900
900 X 12001200 X 12001500 X 12001800 X 12002400 X 1200
900 X 15001200 X 15001500 X 15001800 X 15002400 X 1500
900 X 18001200 X 18001500 X 18001800 X 18002400 X 1800
900 X 21001200 X 21001500 X 21001800 X 21002400 X 2100
900 X 24001200 X 24001500 X 24001800 X 24002400 X 2400
900 X 27001200 X 27001500 X 27001800 X 27002400 X 2700
900 X 30001200 X 30001500 X 30001800 X 30002400 X 3000
900 X 33001200 X 33001500 X 33001800 X 33002400 X 3300
900 X 36001200 X 36001500 X 36001800 X 36002400 X 3600
900 X 39001200 X 39001500 X 39001800 X 39002400 X 3900
900 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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]

</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
Click to expand...


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
blitssman said:
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
Click to expand...
You are very welcome! Glad you figured it out. :)

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

Cheers

shane
Click to expand...

blitssman said:
in this formula above, where do excel find its range (list) or where do i entered the list range in the formula?
Click to expand...
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
You must log in or register to reply here.

Similar threads

H
  • Solved
autofill for 11,200 rows based on matching between two sheets with another
Replies
8
Views
466
Hasson
H
M
  • Solved
filter data based on month or whole data across sheets using dropdown
2
Replies
10
Views
789
Akuini
Akuini
L
  • Question Question
Code revision for transferring/clearing data between worksheets
Replies
4
Views
512
Lil Stinker
L
C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp
A
  • Solved
Replace formula with zero when populate Right mark for two sheets
Replies
3
Views
379
awoohaw
awoohaw

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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