#### Transforming a table into a formula with If function

```I try to define a formula to reflect the table with "IF" or other functions

NUMBER OF HINGES PER DOOR

DOOR HEIGHT
2400     4    4    4    5    6    6    7    7
2200     4    4    4    5    6    6    7    7
2000     4    4    4    5    5    6    7    7
1800     4    4    4    5    5    6    6    7
1600     4    4    4    4    5    6    6    7
1400     3    3    4    4    5    5    6    7
1200     3    3    4    4    5    5    6    6
1000     3    3    4    4    5    5    6    6
800     2    3    4    4    5    5    6    6
600     2    3    4    4    4    5    6
400     2    3    4    4    4
200     2    3
300  400  500  600  700  800  900  1000  DOOR WIDTH

Thank you very much for your help.
```
 0
bchiorean (2)
10/28/2003 7:19:48 PM
excel.misc 78881 articles. 5 followers.

3 Replies
375 Views

Similar Articles

[PageSpeed] 3

```With your data table starting in B9, ie the 2400 is in B9, label A1 as WIDTH, A2
as HEIGHT, A3 as HINGES.  Then in B1 put say 600, B2 say 1200, and in B3 put the
following:-

=VLOOKUP(B2,B9:J20,MATCH(B1,B21:J21,0),0)

Now just change the values in B1 and B2 to get the various Hinge requirements.

--
Regards
Ken.......................    Microsoft MVP - Excel
Sys Spec - Win XP Pro /  XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

"Bogdan" <bchiorean@dynamicstorefixtures.com> wrote in message
> I try to define a formula to reflect the table with "IF" or other functions
>
> NUMBER OF HINGES PER DOOR
>
> DOOR HEIGHT
>   2400     4    4    4    5    6    6    7    7
>   2200     4    4    4    5    6    6    7    7
>   2000     4    4    4    5    5    6    7    7
>   1800     4    4    4    5    5    6    6    7
>   1600     4    4    4    4    5    6    6    7
>   1400     3    3    4    4    5    5    6    7
>   1200     3    3    4    4    5    5    6    6
>   1000     3    3    4    4    5    5    6    6
>    800     2    3    4    4    5    5    6    6
>    600     2    3    4    4    4    5    6
>    400     2    3    4    4    4
>    200     2    3
>           300  400  500  600  700  800  900  1000  DOOR WIDTH
>
> Thank you very much for your help.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.532 / Virus Database: 326 - Release Date: 27/10/2003

```
 0
ken.wright (2489)
10/28/2003 7:30:53 PM
```Thank you for answering my post.It is intresting for a beginer like
me.
The only thing I would like to have is a math or logical formula to
replace the table(there is no table).EX: we introduce the numbers in
the formula and the resulats to give us the table, if we need one.
Maybe you can help me out.

Bogdan

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message news:<e5J0DoYnDHA.2080@TK2MSFTNGP10.phx.gbl>...
> With your data table starting in B9, ie the 2400 is in B9, label A1 as WIDTH, A2
> as HEIGHT, A3 as HINGES.  Then in B1 put say 600, B2 say 1200, and in B3 put the
> following:-
>
> =VLOOKUP(B2,B9:J20,MATCH(B1,B21:J21,0),0)
>
> Now just change the values in B1 and B2 to get the various Hinge requirements.
>
> --
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                    Sys Spec - Win XP Pro /  XL2K & XLXP
>
> ----------------------------------------------------------------------------
>   Attitude - A little thing that makes a BIG difference
> ----------------------------------------------------------------------------
>
>
>
> "Bogdan" <bchiorean@dynamicstorefixtures.com> wrote in message
> > I try to define a formula to reflect the table with "IF" or other functions
> >
> > NUMBER OF HINGES PER DOOR
> >
> > DOOR HEIGHT
> >   2400     4    4    4    5    6    6    7    7
> >   2200     4    4    4    5    6    6    7    7
> >   2000     4    4    4    5    5    6    7    7
> >   1800     4    4    4    5    5    6    6    7
> >   1600     4    4    4    4    5    6    6    7
> >   1400     3    3    4    4    5    5    6    7
> >   1200     3    3    4    4    5    5    6    6
> >   1000     3    3    4    4    5    5    6    6
> >    800     2    3    4    4    5    5    6    6
> >    600     2    3    4    4    4    5    6
> >    400     2    3    4    4    4
> >    200     2    3
> >           300  400  500  600  700  800  900  1000  DOOR WIDTH
> >
> > Thank you very much for your help.
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.532 / Virus Database: 326 - Release Date: 27/10/2003
```
 0
bchiorean (2)
10/29/2003 2:42:34 PM
```What are the criteria for determining how many hinges a door should have? ie how
do you tell from a height and width perspective how many there should be?

--
Regards
Ken.......................    Microsoft MVP - Excel
Sys Spec - Win XP Pro /  XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

"Bogdan" <bchiorean@dynamicstorefixtures.com> wrote in message
> Thank you for answering my post.It is intresting for a beginer like
> me.
> The only thing I would like to have is a math or logical formula to
> replace the table(there is no table).EX: we introduce the numbers in
> the formula and the resulats to give us the table, if we need one.
> Maybe you can help me out.
>
> Bogdan
>
>
>
> "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:<e5J0DoYnDHA.2080@TK2MSFTNGP10.phx.gbl>...
> > With your data table starting in B9, ie the 2400 is in B9, label A1 as
WIDTH, A2
> > as HEIGHT, A3 as HINGES.  Then in B1 put say 600, B2 say 1200, and in B3 put
the
> > following:-
> >
> > =VLOOKUP(B2,B9:J20,MATCH(B1,B21:J21,0),0)
> >
> > Now just change the values in B1 and B2 to get the various Hinge
requirements.
> >
> > --
> > Regards
> >            Ken.......................    Microsoft MVP - Excel
> >                    Sys Spec - Win XP Pro /  XL2K & XLXP
> >
> > ----------------------------------------------------------------------------
> >   Attitude - A little thing that makes a BIG difference
> > ----------------------------------------------------------------------------
> >
> >
> >
> > "Bogdan" <bchiorean@dynamicstorefixtures.com> wrote in message
> > > I try to define a formula to reflect the table with "IF" or other
functions
> > >
> > > NUMBER OF HINGES PER DOOR
> > >
> > > DOOR HEIGHT
> > >   2400     4    4    4    5    6    6    7    7
> > >   2200     4    4    4    5    6    6    7    7
> > >   2000     4    4    4    5    5    6    7    7
> > >   1800     4    4    4    5    5    6    6    7
> > >   1600     4    4    4    4    5    6    6    7
> > >   1400     3    3    4    4    5    5    6    7
> > >   1200     3    3    4    4    5    5    6    6
> > >   1000     3    3    4    4    5    5    6    6
> > >    800     2    3    4    4    5    5    6    6
> > >    600     2    3    4    4    4    5    6
> > >    400     2    3    4    4    4
> > >    200     2    3
> > >           300  400  500  600  700  800  900  1000  DOOR WIDTH
> > >
> > > Thank you very much for your help.
> >
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.532 / Virus Database: 326 - Release Date: 27/10/2003

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.532 / Virus Database: 326 - Release Date: 27/10/2003

```
 0
ken.wright (2489)
10/29/2003 5:30:05 PM

Similar Artilces:

Extend Formula Results
I have a "=NOW()" function formatted to return the date in a cell tha has a column width of about 10. This is not enough space to return long date and the date does not extend into the next cell. How can see my entire date without the aggravating "#####"? Thanks, Kevi -- Message posted from http://www.ExcelForum.com Thanks! That did the trick -- Message posted from http://www.ExcelForum.com ...

Exceed 7 nested formula's (apparently)
I'm creating a formula which when there is an "X" in D8, it looks at B5 which is the year and if that year matched with a heading in Row 6, take the relevant price, if no match leave blank. I need to match 8 years (2010 - 2017) and the formula will not allow me to match more than 3, does anyone have a suggestion of how I can change my formula to include all years ? =IF((D8="x"),IF(\$B\$5=\$J\$6,J8,IF(\$B\$5=\$K\$6,K8,IF(\$B\$5=\$L\$6,L8)))," ") Thanks so much =IF(D8<>"x","",IF(ISNA(MATCH(\$B\$5,\$J\$6:\$Q\$6,0)),"", IN...

MOD
when i apply the MOD function in a number>100000000000 returns #NUM -- Message posted from http://www.ExcelForum.com Hi this is one annoying problem of Excel's MOD implementation. It can't handle large numbers -- Regards Frank Kabel Frankfurt, Germany > when i apply the MOD function in a number>100000000000 returns #NUM! > > > --- > Message posted from http://www.ExcelForum.com/ Here's a good kb article: XL: MOD() Function Returns #NUM! Error Value http://support.microsoft.com/default.aspx?scid=kb;EN-US;119083 -- Dana DeLouis Using Windows XP &...

Copy down formula macro
Hi I have this macro that doesn't work and I am not sure why: LastRow = Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Row Sheets("Sheet1").Range("P2:q2").Copy _ Destination:=Sheets("Sheet1").Range("P3:Q" & LastRow) Calculate Macro should check how many rows are in column D and then copy the formula form P2:Q2 down to the last row. -- Greatly appreciated Eva Hi, In what way doesn't it work because it looks fine to me? Mike "Eva" wrote: > Hi > I have this macro that...

Median If Function
Hi! I am working on a statistics report, and I need to find the median of a percentage increase, decrease, and no change. Would these be the formulas I need to use? =MEDIAN(IF(C160:CI160,">0")) for increase =MEDIAN(IF(C160:CI160,"<0")) for decrease =MEDIAN(IF(C160:CI160,"0")) for no change Thanks so much for your help! ~Allison Try =MEDIAN(IF(C160:CI160>0,C160:CI160)) etc., which is an array formula -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "allyrose79" <allyros...

Disable Tables
Hi, I have a database which links to another database on the network. I have linked 5 tables from database1 into database2. Users do not have access to database2. Only I will have access. Currently, users can not edit the design of the 5 tables, since they are linked tables. However, they are able to open and view everything in the tables. I would like to completely disable this. There are queries which pull specific information for each user from these 5 tables and this is the only way that I would like users to view information. I do not want them opening the original tables at ...

Macro for Pivot Tables
Hi, I'm having trouble building a macro on pivot tables. I hope some experts here can help solve my problem. Any inputs will be greatly appreciated!! I'm trying to build a macro to refresh data in multiple pivot tables. The pivot tables are build below one another, and additional rows are inserted between pivot tables. When i built a macro to link all tables to the data in the first (master) pivot table, I found Excel was refreshing the table based on the exact cell I was clicking. This has become a problem since new rows are inserted between tables when new data comes in, and...

Struggling with IF formula...is it even the right one for me?
Let's say that I have a value in one cell, call it X. X is a numerical value that needs not be touched or modified UNLESS it exceeds another numerical value in another cell, call it Y. If it DOES exceed Y, then it must be made to show as EQUAL to the value in Y. If it doesn't, it can show whatever it is in reality. How do I sculpt a formula for this? On Apr 9, 6:56 pm, Corradus <Corra...@discussions.microsoft.com> wrote: > Let's say that I have a value in one cell, call it X. X is a numerical value > that needs not be touched or modified UNLESS it exceeds anot...

string function to wrap with designated delimiter
I need a string function that can wrap a string into an array of substrings. The function should take a delimiter character and a max length parameter and return the array of strings that minimizes the number of array elements, each of which would terminate with that delimiter, except when the max length requires a break in the middle of the string not at a delimiter, and allowing that the final element might not terminate with a delimiter character. For example given the delimiter '\', a max length of 30 and the string 'C:\Documents and Settings\Larry\My Documents\...

Formatting Charts from Pivot Table...
I've created a pivot table/with chart. The default chart was a bar chart, which I've changed to a pie chart. The problem is I don't like the default colors so I've changed them to the way I like them; however, once I save the document they go back to the default colors. Is there any way to keep the revised colors, rather than the default colors? Thanks, KSL. ...

Pasting a Word Table into Excel
I have a Word table that I need to paste into Excel. When I paste, the length of the rows of the table are much larger than they were in the Word table. I can double click on the row to make them the default size but I have to do each one separately. Is there a way to make the rows go to the default size all at once? I am using Excel 2002. Thank you for any help. Best regards Dee If you hit ctrl-a (twice in xl2003), you select all the cells. Now try double clicking on line between the row numbers on the row header (to the far left). Or just format|row|autofit (with all the rows selec...

custom function-code for percentage
I am new to VBA. However I have created a custom function using VBA and the code is detailed below. Public Function PLF(UnitsGenerated, PlantCapacity) ' This user defined function provides the PLF of the Plant in two decimals PLF = (UnitsGenerated / PlantCapacity) * 8.76 PLF = Application.Round(PLF, 2) End Function The result of this custom function is shown in two digits. But I want a percent symbol along with this result. Can anyone would suggest me the code to be added for this for which I thank you in advance. seenu Format the cell as a percentage and change ...

Pass Through Make Table
Pass Through Make Table Hello, I have a pass through query object; I also have a make table query object based off the pass through query. This works just fine to make an Access table with ORACLE data from my pass through query. My problem/issue: I must get this into VBA. Can anyone tell me how to put the pass through and make table queries into VBA? The ODBC Connect Str looks like this: ODBC; DSN=SERVER;UID=LOGIN;PWN=MYPSWRD;SERVER=SERVERNAME Thanks, alex "alex" <sql_aid@yahoo.com> wrote in message news:791844cd-0798-44c9-918f-2ca597758053@a6g2000y...

Formula fill across workbooks
I have been trying to get the formula fill to work across workbooks, but all it does is copy the cells I am using as a start point. My data source is nine cell on one work book (1 - 9), and I did a simple =[Book1]Sheet1!\$A\$1 in the first cell o fwork book 2, followed by =[Book1]Sheet1!\$A\$2 and =[Book1]Sheet1!\$A\$3 in the next two cells. It worked fine for the first three cell, but when I tried to use the formula fill all it did was copy the original three cells. What am I missing? You have too many \$ signs. =[Book1]Sheet1!\$A\$1 Change to =[Book1]Sheet1!\$A1 and drag/copy...

Totalling seperate cells from a HLOOKUP function
is it possible to sum the product of 2 or more cells, 1 cell being the result of a HLOOKUP function, without creating a third cell for individual results? Here is my problem: Row A contains a list of different locations Row B contains wether the delivery has failed or completed Row C contains the quantity of items on Invoice Is there a formula I can use to have the total quantity from all of the failed deliveries for that day? As the failed delivery locations may vary from day to day, I cannot just total those cells from each location I have tried this formula, but it re...

Problem with copying formula
I am entering the following formula into Cell L3.... =B4-E3 When I copy this down to the cells below with the drag handle, the formula is copied correctly (i.e =B5-E4, =B6-E5 etc..), but the result is the same in every cell which is clearly wrong. I'm stumped. Thanks.:confused: -- jimbob ------------------------------------------------------------------------ jimbob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29107 View this thread: http://www.excelforum.com/showthread.php?threadid=488480 Set calculation to automatic under tools>calculation or p...

pivot table based on 4 others (in seperate workbooks)?
basically I have aspreadsheet for each week with people and hours in them, and in each weekly spreadsheet i have a pivot table which adds up and summarises the hours for the people for that week. What I need to do is get a pivottable which adds together those 4 pivot tables to give me the same summary, but for all 4 weeks together. I dont seem to be able to make a pivot table based on 4 pivot tables in other workbooks, so can anyone suggest the way to solve this? thanks -- neowok ------------------------------------------------------------------------ neowok's Profile: http://www.e...

pasting values from pivot table still linked
I have a fairly big table that provides values for a pivot table. I would like to view the data from the pivot table and copy and paste this information to a new tab as values, and retain the formatting. The goal is to keep the spreadsheet from freezing - which it seems to do too much. I understand that pivot tables share cached memory. This copy, paste special, values and paste special "all using source theme" method seemed to be working. However, I noticed tha the sheet was freezing more and more and today, I noticed that when I updated the pivot table for t...

days used in a formula
does someone know how to insert the day of the month in a formula ? example =SUM(E26:E99)/here should be today's date Thanks for any help John- today() returns todays date day(today()) returns the day of the month weekday(today()) returns the weekday of the date Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27107 View this thread: http://www.excelforum.com/showthread.php?threadid=556350 =SUM(E26:E99)/DAY(TODAY()) -- HTH Bob Phillips (replac...

WNet... functions in windows 98?
My program that uses WNet... functions (WNetOpenEnum, WNetEnumResource...) works only under windows 2000 and windows XP. Is there a way to use these functions with win 98 and if not, than how else can I enumerate network resources? Tnx in advance. Dragan ...

Excel 2007 formula result
When extending a formula to multiple cells and hit enter, the correct answer does not appear until I close document and save changes. Then when trying to add complete column of the answers obtained with fill down formula, answer is 0(zero) Any ideas will be appreciated. -- handmanager Sounds like you have calculation set to manual. Press F9 to recalculate the worksheet. "handmgr" <DaytonOH> wrote in message news:2C9DCC4D-C2D4-4A95-9F95-5A787EC78F35@microsoft.com... > When extending a formula to multiple cells and hit enter, the correct > answer > does not app...