Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can you use multiple ranges in a function?
I am looking for a function to search through multiple ranges of cells and return a number if it finds any instances of that number. For instance: I have say 6 cells, (A1, C17, F13:G14) and I want to find out if any of them contain the number "1" and if they do, I want the result of B2 to be "1". The way I thought I could accomplish this was to go to cell B2 and do the formula =if(countif((A1,C17,F13:G14),"1")0,1,0) This seems to work for a single range such as =if(countif(F13:G14,"1")0,1,0) I just dont know the proper syntax for including multiple ranges in a function. If anyone knows how to include multiple ranges in a function (if its possible), or knows a simpler way to do what I am trying to do, the help would be greatly appreciated. -- solinar ------------------------------------------------------------------------ solinar's Profile: http://www.excelforum.com/member.php...o&userid=31159 View this thread: http://www.excelforum.com/showthread...hreadid=508216 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can you use multiple ranges in a function?
Hi!
Try this: =IF(OR(A1=1,C17=1,COUNTIF(F13:G14,1)),1,0) Biff "solinar" wrote in message ... I am looking for a function to search through multiple ranges of cells and return a number if it finds any instances of that number. For instance: I have say 6 cells, (A1, C17, F13:G14) and I want to find out if any of them contain the number "1" and if they do, I want the result of B2 to be "1". The way I thought I could accomplish this was to go to cell B2 and do the formula =if(countif((A1,C17,F13:G14),"1")0,1,0) This seems to work for a single range such as =if(countif(F13:G14,"1")0,1,0) I just dont know the proper syntax for including multiple ranges in a function. If anyone knows how to include multiple ranges in a function (if its possible), or knows a simpler way to do what I am trying to do, the help would be greatly appreciated. -- solinar ------------------------------------------------------------------------ solinar's Profile: http://www.excelforum.com/member.php...o&userid=31159 View this thread: http://www.excelforum.com/showthread...hreadid=508216 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can you use multiple ranges in a function?
=IF(OR(A1=1, C17=1, count(F13:G14,1)0), 1, "what to do otherwise")
=--(A1=1)+--(C17=1)+--(COUNTIF(F13:G14,1)1) will return 1 or 0 Why did you put quotes around the number 1 - quotes generally needed only with text best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "solinar" wrote in message ... I am looking for a function to search through multiple ranges of cells and return a number if it finds any instances of that number. For instance: I have say 6 cells, (A1, C17, F13:G14) and I want to find out if any of them contain the number "1" and if they do, I want the result of B2 to be "1". The way I thought I could accomplish this was to go to cell B2 and do the formula =if(countif((A1,C17,F13:G14),"1")0,1,0) This seems to work for a single range such as =if(countif(F13:G14,"1")0,1,0) I just dont know the proper syntax for including multiple ranges in a function. If anyone knows how to include multiple ranges in a function (if its possible), or knows a simpler way to do what I am trying to do, the help would be greatly appreciated. -- solinar ------------------------------------------------------------------------ solinar's Profile: http://www.excelforum.com/member.php...o&userid=31159 View this thread: http://www.excelforum.com/showthread...hreadid=508216 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can you use multiple ranges in a function?
Hi: Thanks for the response, but I tried the countif function because using if and or functions were too cumbersome. My actual problem has about 10 different ranges to search and I need to perform 27 different instances of this. I was hoping to find something easier than typing a logic statement for each of the 10 ranges (and then modifying that for each of the 27 instances I have to do it in). Thanks anyway for the reply though. -- solinar ------------------------------------------------------------------------ solinar's Profile: http://www.excelforum.com/member.php...o&userid=31159 View this thread: http://www.excelforum.com/showthread...hreadid=508216 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can you use multiple ranges in a function?
My actual problem has about 10
different ranges to search and I need to perform 27 different instances of this. Then why didn't you "say" that in the first place? <g There is no elegant way to do this! You're going to end up with a long ugly formula. The OR function can have up to 30 arguments so you're well within that limit with just 10. Biff "solinar" wrote in message ... Hi: Thanks for the response, but I tried the countif function because using if and or functions were too cumbersome. My actual problem has about 10 different ranges to search and I need to perform 27 different instances of this. I was hoping to find something easier than typing a logic statement for each of the 10 ranges (and then modifying that for each of the 27 instances I have to do it in). Thanks anyway for the reply though. -- solinar ------------------------------------------------------------------------ solinar's Profile: http://www.excelforum.com/member.php...o&userid=31159 View this thread: http://www.excelforum.com/showthread...hreadid=508216 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can you use multiple ranges in a function?
Biff wrote...
My actual problem has about 10 different ranges to search and I need to perform 27 different instances of this. Then why didn't you "say" that in the first place? <g There is no elegant way to do this! You're going to end up with a long ugly formula. The OR function can have up to 30 arguments so you're well within that limit with just 10. .... There isn't?! If all the ranges involved were in a single worksheet, consider =IF(INDEX(FREQUENCY((rng1,rng2,rng3,rng4),x+10^INT (LOG(x)-14)*{-1;0;1}),2),1,0) And multiple area ranges aren't subject to a limit of 30 or fewer areas. They're only subject to the limit on formula length, and that could be extended using defined names. Consider =IF(INDEX(FREQUENCY((B2,C2,D2,E2,F2,G2,H2,I2,J2,K2 ,L2,M2, B3,C3,D3,E3,F3,G3,H3,I3,J3,K3,L3,M3,B4,C4,D4,E4,F4 ,G4,H4,I4,J4,K4,L4,M4, B5,C5,D5,E5,F5,G5,H5,I5,J5,K5,L5,M5),x+10^INT(LOG( x)-14)*{-1;0;1}),2),1,0) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can you use multiple ranges in a function?
There is no elegant way to do this!
There isn't?! I guess it depends on ones definition of elegant! Biff "Harlan Grove" wrote in message ups.com... Biff wrote... My actual problem has about 10 different ranges to search and I need to perform 27 different instances of this. Then why didn't you "say" that in the first place? <g There is no elegant way to do this! You're going to end up with a long ugly formula. The OR function can have up to 30 arguments so you're well within that limit with just 10. ... There isn't?! If all the ranges involved were in a single worksheet, consider =IF(INDEX(FREQUENCY((rng1,rng2,rng3,rng4),x+10^INT (LOG(x)-14)*{-1;0;1}),2),1,0) And multiple area ranges aren't subject to a limit of 30 or fewer areas. They're only subject to the limit on formula length, and that could be extended using defined names. Consider =IF(INDEX(FREQUENCY((B2,C2,D2,E2,F2,G2,H2,I2,J2,K2 ,L2,M2, B3,C3,D3,E3,F3,G3,H3,I3,J3,K3,L3,M3,B4,C4,D4,E4,F4 ,G4,H4,I4,J4,K4,L4,M4, B5,C5,D5,E5,F5,G5,H5,I5,J5,K5,L5,M5),x+10^INT(LOG( x)-14)*{-1;0;1}),2),1,0) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can you use multiple ranges in a function?
Excellent. It seems the frequency function has no problem with dealing with multiple ranges separated by commas within quotes. This did the trick. Thanks all for the help! -- solinar ------------------------------------------------------------------------ solinar's Profile: http://www.excelforum.com/member.php...o&userid=31159 View this thread: http://www.excelforum.com/showthread...hreadid=508216 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating a function with multiple criteria | Excel Worksheet Functions | |||
IF function....testing against values in multiple cells | Excel Worksheet Functions | |||
linest won't accept multiple reference ranges | Excel Worksheet Functions | |||
Multiple OR function | Excel Worksheet Functions | |||
Countif & ranges consisting of multiple areas | Excel Worksheet Functions |