Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone see a problem with this?
I've currently got 2 sheets and i'm comparing 2 cells in one worksheet to 2 cells in another, if they match then the output is the 3rd cell, or another if statement if false. The if statements work if i terminate the fomula on number 9, if i add 10 the formula fails, if i do all the clicking on cells to let excel add the rest of the formula for 10 it also fails. I've put each nested if/and on a separate line for ease of reading, the double space between 9 and 10 signifies the point where it stops working. Any help/info is much appreciated as its driving me batty. Thanks =IF(AND(Blank!B18=Miles!$B$3,Blank!C18=Miles!$C$3) ,Miles!$D$3, IF(AND(Blank!B18=Miles!$B$4,Blank!C18=Miles!$C$4), Miles!$D$4, IF(AND(Blank!B18=Miles!$B$5,Blank!C18=Miles!$C$5), Miles!$D$5, IF(AND(Blank!B18=Miles!$B$6,Blank!C18=Miles!$C$6), Miles!$D$6, IF(AND(Blank!B18=Miles!$B$7,Blank!C18=Miles!$C$7), Miles!$D$7, IF(AND(Blank!B18=Miles!$B$8,Blank!C18=Miles!$C$8), Miles!$D$8, IF(AND(Blank!B18=Miles!$B$9,Blank!C18=Miles!$C$9), Miles!$D$9, IF(AND(Blank!B18=Miles!$B$10,Blank!C18=Miles!$C$10 ),Miles!$D$10, IF(AND(Blank!B18=Miles!$B$11,Blank!C18=Miles!$C$11 ),Miles!$D$11, IF(AND(Blank!B18=Miles!$B$12,Blank!C18=Miles!$C$12 ),Miles!$D$12, IF(AND(Blank!B18=Miles!$B$13,Blank!C18=Miles!$C$13 ),Miles!$D$13, IF(AND(Blank!B18=Miles!$B$14,Blank!C18=Miles!$C$14 ),Miles!$D$14, IF(AND(Blank!B18=Miles!$B$15,Blank!C18=Miles!$C$15 ),Miles!$D$15, IF(AND(Blank!B18=Miles!$B$16,Blank!C18=Miles!$C$16 ),Miles!$D$16,0)))))))))))))) -- bikky ------------------------------------------------------------------------ bikky's Profile: http://www.excelforum.com/member.php...o&userid=36149 View this thread: http://www.excelforum.com/showthread...hreadid=559359 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone see a problem with this?
hi,
you have broken the if limit, excel just support 7 if's in a formula hth regards from Brazil Marcelo "bikky" escreveu: I've currently got 2 sheets and i'm comparing 2 cells in one worksheet to 2 cells in another, if they match then the output is the 3rd cell, or another if statement if false. The if statements work if i terminate the fomula on number 9, if i add 10 the formula fails, if i do all the clicking on cells to let excel add the rest of the formula for 10 it also fails. I've put each nested if/and on a separate line for ease of reading, the double space between 9 and 10 signifies the point where it stops working. Any help/info is much appreciated as its driving me batty. Thanks =IF(AND(Blank!B18=Miles!$B$3,Blank!C18=Miles!$C$3) ,Miles!$D$3, IF(AND(Blank!B18=Miles!$B$4,Blank!C18=Miles!$C$4), Miles!$D$4, IF(AND(Blank!B18=Miles!$B$5,Blank!C18=Miles!$C$5), Miles!$D$5, IF(AND(Blank!B18=Miles!$B$6,Blank!C18=Miles!$C$6), Miles!$D$6, IF(AND(Blank!B18=Miles!$B$7,Blank!C18=Miles!$C$7), Miles!$D$7, IF(AND(Blank!B18=Miles!$B$8,Blank!C18=Miles!$C$8), Miles!$D$8, IF(AND(Blank!B18=Miles!$B$9,Blank!C18=Miles!$C$9), Miles!$D$9, IF(AND(Blank!B18=Miles!$B$10,Blank!C18=Miles!$C$10 ),Miles!$D$10, IF(AND(Blank!B18=Miles!$B$11,Blank!C18=Miles!$C$11 ),Miles!$D$11, IF(AND(Blank!B18=Miles!$B$12,Blank!C18=Miles!$C$12 ),Miles!$D$12, IF(AND(Blank!B18=Miles!$B$13,Blank!C18=Miles!$C$13 ),Miles!$D$13, IF(AND(Blank!B18=Miles!$B$14,Blank!C18=Miles!$C$14 ),Miles!$D$14, IF(AND(Blank!B18=Miles!$B$15,Blank!C18=Miles!$C$15 ),Miles!$D$15, IF(AND(Blank!B18=Miles!$B$16,Blank!C18=Miles!$C$16 ),Miles!$D$16,0)))))))))))))) -- bikky ------------------------------------------------------------------------ bikky's Profile: http://www.excelforum.com/member.php...o&userid=36149 View this thread: http://www.excelforum.com/showthread...hreadid=559359 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone see a problem with this?
Bikky, The limit for nesting IF formulas is 7. After that it quits working. Type "Excel specifications and limits" into Help and you will see additional limitations along with this one. Nested levels of functions 7 Describe what you are trying to accomplish and someone might have an alternate method, but the route your going won't work. HTH -- Casey ------------------------------------------------------------------------ Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545 View this thread: http://www.excelforum.com/showthread...hreadid=559359 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone see a problem with this?
Thanks, i think it might have to be vb. Its basically a milage form to submit for work, i have a sheet with a named column for a validation drop down list for to and from, then also on that sheet with the validation list is the miles accrued between sites. eg. sitea siteb 10 sitea sitec 12 sitea sited 27 siteb sitec 19 siteb sited 12 sitec sited 6 so if cell 1 = miles!cell1 and cell2 = miles!cell2 then cell3, else if cell 1=miles!cell4 and cell2 = miles!cell5 then cell6, else...... Problem is with so many sites it would be a large if, and i didn't know what the limit was. I need the local cell reference to be dynamic so it can be copied from a blank template in the workbook to a new worksheet for each month, however the lookup is to a fixed cell called miles. Any help on this would be great as we're all sick of handwriting these every month. Thanks -- bikky ------------------------------------------------------------------------ bikky's Profile: http://www.excelforum.com/member.php...o&userid=36149 View this thread: http://www.excelforum.com/showthread...hreadid=559359 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone see a problem with this?
It is because you areonly allowed 7 levels of nesting in excel, thats where you formula fails However you could use another formula =IF(ISERROR(MATCH(blank!b18,Miles!B3:b16,0)=MATCH( blank!c18,Miles!c3:c16,0)),0,OFFSET(miles!d2,MATCH (blank!b18,Miles!b3:b16,0),0)) That should work if I have typed it correctly Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=559359 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone see a problem with this?
Here's a really ugly, inefficient formula which does what you asked: =IF(ISERROR(VLOOKUP(Blank!$B$18,Miles!$B$3:$D$16,3 ,FALSE)),0,IF(ISERROR(VLOOKUP(Blank!$C$18,Miles!$C $3:$D$16,2,FALSE)),0,IF(VLOOKUP(Blank!$B$18,Miles! $B$3:$D$16,3,FALSE)=VLOOKUP(Blank!$C$18,Miles!$C$3 :$D$16,2,FALSE),VLOOKUP(Blank!$C$18,Miles!$C$3:$D$ 16,2,FALSE),0))) And here's an easier method: 1. On the MILES sheet, enter this formula in the first free column (I'll assume it's column E): =IF(AND(Miles!B3=Blank!B18,Miles!C3=Blank!C18),Mil es!D3,0) Copy this formula down for as many rows as needed (through row 16 in your original post). 2. In the cell where your IF formula was , enter: =SUM(Miles!E:E) If the BLANK sheet formulas are in a different column than E, change E:E to that column. Hope this helps, Hutch "bikky" wrote: Thanks, i think it might have to be vb. Its basically a milage form to submit for work, i have a sheet with a named column for a validation drop down list for to and from, then also on that sheet with the validation list is the miles accrued between sites. eg. sitea siteb 10 sitea sitec 12 sitea sited 27 siteb sitec 19 siteb sited 12 sitec sited 6 so if cell 1 = miles!cell1 and cell2 = miles!cell2 then cell3, else if cell 1=miles!cell4 and cell2 = miles!cell5 then cell6, else...... Problem is with so many sites it would be a large if, and i didn't know what the limit was. I need the local cell reference to be dynamic so it can be copied from a blank template in the workbook to a new worksheet for each month, however the lookup is to a fixed cell called miles. Any help on this would be great as we're all sick of handwriting these every month. Thanks -- bikky ------------------------------------------------------------------------ bikky's Profile: http://www.excelforum.com/member.php...o&userid=36149 View this thread: http://www.excelforum.com/showthread...hreadid=559359 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone see a problem with this?
Oops! The BLANK sheet reference needs to be absolute: =IF(AND(Miles!B3=Blank!$B$18,Miles!C3=Blank!$C$18) ,Miles!D3,0) I like Dav's formula. My first thought was to use Match, but then I thought of Vlookup. Should have gone with the first impulse. Hutch "Tom Hutchins" wrote: Here's a really ugly, inefficient formula which does what you asked: =IF(ISERROR(VLOOKUP(Blank!$B$18,Miles!$B$3:$D$16,3 ,FALSE)),0,IF(ISERROR(VLOOKUP(Blank!$C$18,Miles!$C $3:$D$16,2,FALSE)),0,IF(VLOOKUP(Blank!$B$18,Miles! $B$3:$D$16,3,FALSE)=VLOOKUP(Blank!$C$18,Miles!$C$3 :$D$16,2,FALSE),VLOOKUP(Blank!$C$18,Miles!$C$3:$D$ 16,2,FALSE),0))) And here's an easier method: 1. On the MILES sheet, enter this formula in the first free column (I'll assume it's column E): =IF(AND(Miles!B3=Blank!B18,Miles!C3=Blank!C18),Mil es!D3,0) Copy this formula down for as many rows as needed (through row 16 in your original post). 2. In the cell where your IF formula was , enter: =SUM(Miles!E:E) If the BLANK sheet formulas are in a different column than E, change E:E to that column. Hope this helps, Hutch "bikky" wrote: Thanks, i think it might have to be vb. Its basically a milage form to submit for work, i have a sheet with a named column for a validation drop down list for to and from, then also on that sheet with the validation list is the miles accrued between sites. eg. sitea siteb 10 sitea sitec 12 sitea sited 27 siteb sitec 19 siteb sited 12 sitec sited 6 so if cell 1 = miles!cell1 and cell2 = miles!cell2 then cell3, else if cell 1=miles!cell4 and cell2 = miles!cell5 then cell6, else...... Problem is with so many sites it would be a large if, and i didn't know what the limit was. I need the local cell reference to be dynamic so it can be copied from a blank template in the workbook to a new worksheet for each month, however the lookup is to a fixed cell called miles. Any help on this would be great as we're all sick of handwriting these every month. Thanks -- bikky ------------------------------------------------------------------------ bikky's Profile: http://www.excelforum.com/member.php...o&userid=36149 View this thread: http://www.excelforum.com/showthread...hreadid=559359 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone see a problem with this?
Dav,
I tried your formula and it didn't work. If column B matches, it returns the column D value regardless if column C matches or not. FALSE is not an error. I rewrote it as follows, and it works: =IF(MATCH(Blank!B18,Miles!B3:B16,0)=MATCH(Blank!C1 8,Miles!C3:C16,0),OFFSET(Miles!D2,MATCH(Blank!B18, Miles!B3:B16,0),0),0) Hutch "Dav" wrote: It is because you areonly allowed 7 levels of nesting in excel, thats where you formula fails However you could use another formula =IF(ISERROR(MATCH(blank!b18,Miles!B3:b16,0)=MATCH( blank!c18,Miles!c3:c16,0)),0,OFFSET(miles!d2,MATCH (blank!b18,Miles!b3:b16,0),0)) That should work if I have typed it correctly Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=559359 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone see a problem with this?
WOahhh Thanks for all the help you've supplied, i'll run with the match script, as i don't want fixed reference to Blank! as that is the base sheet to be copied to each months new worksheet. The Match isn't quite working yet, as i either get the correct milage for the first entry in the match lookup, or 0 or NA. If your wanting a copy of the doc i can post to a website for you's to have a look, NOTE the miles sheet with the list of sites isn't complete yet, its a work in progress, so i'll have to easily adjust the formulae as more "regular" trips are required. The start and end of journey also needs freetext option for "sporadic" journey's infact check www.lan-uk.derwentside.net/milage.xls (it is virus free, my own hosted server) rather than posting back the spreadsheet, pasting the formula in here would be more helpful for me to learn, and others to reference from. THANKS V MUCH for all the help so far. spencer -- bikky ------------------------------------------------------------------------ bikky's Profile: http://www.excelforum.com/member.php...o&userid=36149 View this thread: http://www.excelforum.com/showthread...hreadid=559359 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone see a problem with this?
I looked at your file. I'm embarassed to say that the dummy data I created
Friday didn't have any duplicate values, and that the solutions offered won't work if there are duplicates. However, here's an easy solution that WILL work: 1. On the MILES sheet, insert a new column between C & D (it becomes your new column D, and the miles to be returned are now column E). In D3, enter: =B3&C3 Copy this formula down for as many rows as have data on the MILES sheet. We are creating a single concatenated field against which we can easily do a Vlookup. 2. On the BLANK sheet, enter the following formula in D18: =IF(ISERROR(VLOOKUP(B18&C18,Miles!D:E,2,FALSE)),"" ,VLOOKUP(B18&C18,Miles!D:E,2,FALSE)) Copy this formula down through all the rows on your form. This formula concatenates the From and To sites, does a Vlookup with it on the MILES sheet, and returns the mileage. If the Vlookup fails (can't find a match), nothing is displayed. You also have a few typos: milage should be mileage monthely should be monthly mileometer should be odometer Hope this helps, Hutch "bikky" wrote: WOahhh Thanks for all the help you've supplied, i'll run with the match script, as i don't want fixed reference to Blank! as that is the base sheet to be copied to each months new worksheet. The Match isn't quite working yet, as i either get the correct milage for the first entry in the match lookup, or 0 or NA. If your wanting a copy of the doc i can post to a website for you's to have a look, NOTE the miles sheet with the list of sites isn't complete yet, its a work in progress, so i'll have to easily adjust the formulae as more "regular" trips are required. The start and end of journey also needs freetext option for "sporadic" journey's infact check www.lan-uk.derwentside.net/milage.xls (it is virus free, my own hosted server) rather than posting back the spreadsheet, pasting the formula in here would be more helpful for me to learn, and others to reference from. THANKS V MUCH for all the help so far. spencer -- bikky ------------------------------------------------------------------------ bikky's Profile: http://www.excelforum.com/member.php...o&userid=36149 View this thread: http://www.excelforum.com/showthread...hreadid=559359 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone see a problem with this?
The easy way is to change you data to like a mileage table in an atlas eg A1 A B C D A 0 12 7 9 B 12 0 15 6 C 7 15 0 16 D 9 6 16 0 with the mileage in the cells if A B C D are the places Then you just use an offset formula if the table has cell a1 where i have coloured ir red offset ($a$1,match(G1,a2:a5,0),match(h1,b1:e1,0)) would return the value where h1 is the starting place and g1 the finish, or the other way round it does not really matter! An index function does a similar thing to the offset and match so that is another option. the table approach means you have to type less as well N/A is returned if there is no match Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=559359 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone see a problem with this?
Thanks V much Tom, That worked perfectly. Now just to populate the Mileage table and get in circulation. Then work on dav's idea of the matrix table. I'll correct the Monthely, the others are as per original sheet (which we're not allowed to doctor in any way other than filling out the claim for mileage). I saw mileometer on my very first form and thought it was wrong. any THANKS all, and i'd Kiss you if i could reach that far... Keep an eye out for another one i'm working on ;) -- bikky ------------------------------------------------------------------------ bikky's Profile: http://www.excelforum.com/member.php...o&userid=36149 View this thread: http://www.excelforum.com/showthread...hreadid=559359 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone see a problem with this?
DAV, Pick your brains a bit more on matrix idea please..... using formula =OFFSET(Matrix!$A$1,MATCH(B18,Matrix!1:1,0),MATCH( C18,Matrix!A:A,0)) is giving me 1 row and 1 col. out so eg a1 a b c d a 0 12 7 9 b 12 0 15 6 c 7 15 0 16 d 9 6 16 0 and the lookup is "a b" which should give the result 12, but i'm getting 15, one col along, plus one row down, and this is for every one. Any ideas where its gone wrong? Thanks again -- bikky ------------------------------------------------------------------------ bikky's Profile: http://www.excelforum.com/member.php...o&userid=36149 View this thread: http://www.excelforum.com/showthread...hreadid=559359 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone see a problem with this?
bikky Wrote: DAV, Pick your brains a bit more on matrix idea please..... using formula =OFFSET(Matrix!$A$1,MATCH(B18,Matrix!1:1,0),MATCH( C18,Matrix!A:A,0)) is giving me 1 row and 1 col. out so eg a1 a b c d a 0 12 7 9 b 12 0 15 6 c 7 15 0 16 d 9 6 16 0 and the lookup is "a b" which should give the result 12, but i'm getting 15, one col along, plus one row down, and this is for every one. Any ideas where its gone wrong? Thanks again for the time being i've used =OFFSET(Matrix!$A$1,MATCH(B18,Matrix!a1:a50,0),MAT CH(C18,Matrix!b1:Az1,0)) this gives me 50 sites in each direction, but still would like to know how to do it with full row/column reference, rather than fixed column/row references if anyone knows -- bikky ------------------------------------------------------------------------ bikky's Profile: http://www.excelforum.com/member.php...o&userid=36149 View this thread: http://www.excelforum.com/showthread...hreadid=559359 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
have some problem with database | Excel Discussion (Misc queries) | |||
Urgent Help Required on Excel Macro Problem | Excel Discussion (Misc queries) | |||
Problem With Reference Update | Excel Worksheet Functions | |||
Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) |