Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match, Index, If
I have a data set that is user based. The answer that i want to bring up is
dependant on 3 variables. The first is the month it is (column), second is the type of day sunny or cloudy (cloumn) and the third is the time of day which is in a row. If anyone could help me with a code taht could be used it would be greatly appreciated. 1 2 3 April Sunny 3.7 3.2 2.8 April Cloudy 4.1 3.7 3.2 July Sunny 16.3 15.7 15.2 July Cloudy 18.6 18.2 17.5 October Sunny 6.9 6.1 5.7 October Cloudy 7.2 6.7 6.1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match, Index, If
Hi,
Here is the basic idea: =SUMPRODUCT(--(A$2:A$16=C1),--(B$2:B$16=C2),--($D$1:$R$1=C3) In this example column A contains date, B contains Cloudy/Sunny, and range D1:R1 contains your third item. The values you want to count for each of these are in C1, C2, and C3. You could hard code these, ie. instead of C1 you could type "March". If this helps, please click the Yes button Cheers, Shane Devenshire "Shortstopper00" wrote: I have a data set that is user based. The answer that i want to bring up is dependant on 3 variables. The first is the month it is (column), second is the type of day sunny or cloudy (cloumn) and the third is the time of day which is in a row. If anyone could help me with a code taht could be used it would be greatly appreciated. 1 2 3 April Sunny 3.7 3.2 2.8 April Cloudy 4.1 3.7 3.2 July Sunny 16.3 15.7 15.2 July Cloudy 18.6 18.2 17.5 October Sunny 6.9 6.1 5.7 October Cloudy 7.2 6.7 6.1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match, Index, If
Hey, I used the code was helpful, but where do I index the values that I want
come up. after the 3 variables are picked by the user "Shane Devenshire" wrote: Hi, Here is the basic idea: =SUMPRODUCT(--(A$2:A$16=C1),--(B$2:B$16=C2),--($D$1:$R$1=C3) In this example column A contains date, B contains Cloudy/Sunny, and range D1:R1 contains your third item. The values you want to count for each of these are in C1, C2, and C3. You could hard code these, ie. instead of C1 you could type "March". If this helps, please click the Yes button Cheers, Shane Devenshire "Shortstopper00" wrote: I have a data set that is user based. The answer that i want to bring up is dependant on 3 variables. The first is the month it is (column), second is the type of day sunny or cloudy (cloumn) and the third is the time of day which is in a row. If anyone could help me with a code taht could be used it would be greatly appreciated. 1 2 3 April Sunny 3.7 3.2 2.8 April Cloudy 4.1 3.7 3.2 July Sunny 16.3 15.7 15.2 July Cloudy 18.6 18.2 17.5 October Sunny 6.9 6.1 5.7 October Cloudy 7.2 6.7 6.1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match, Index, If
Hi,
I don't understand you question. If you want to copy the formula down and use other values rather than C1, C2 and C3 then you could put the three values in three empty columns (say C1:E1) and then each new combination below them then copy the formula down =SUMPRODUCT(--(A$2:A$16=C1),--(B$2:B$16=D1),--($F$1:$T$1=E1) Note: I have moved the last range so it doesn't overlap the C1:E1 range. I don't know where your ranges are actually located. If this helps please click the Yes button. Cheers, Shane Devenshire "Shortstopper00" wrote: Hey, I used the code was helpful, but where do I index the values that I want come up. after the 3 variables are picked by the user "Shane Devenshire" wrote: Hi, Here is the basic idea: =SUMPRODUCT(--(A$2:A$16=C1),--(B$2:B$16=C2),--($D$1:$R$1=C3) In this example column A contains date, B contains Cloudy/Sunny, and range D1:R1 contains your third item. The values you want to count for each of these are in C1, C2, and C3. You could hard code these, ie. instead of C1 you could type "March". If this helps, please click the Yes button Cheers, Shane Devenshire "Shortstopper00" wrote: I have a data set that is user based. The answer that i want to bring up is dependant on 3 variables. The first is the month it is (column), second is the type of day sunny or cloudy (cloumn) and the third is the time of day which is in a row. If anyone could help me with a code taht could be used it would be greatly appreciated. 1 2 3 April Sunny 3.7 3.2 2.8 April Cloudy 4.1 3.7 3.2 July Sunny 16.3 15.7 15.2 July Cloudy 18.6 18.2 17.5 October Sunny 6.9 6.1 5.7 October Cloudy 7.2 6.7 6.1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match, Index, If
Ah, darned, you multi-posted in .misc. Pl refrain from doing this
Below's my response to you over the ----- ---- Assume your reference table as posted is in A1:E7 Assume the triple inputs in G2:I2 a October, Sunny, 2 In J2, normal ENTER: =INDEX(C2:E7,MATCH(1,INDEX((A2:A7=G2)*(B2:B7=H2),) ,0),MATCH(I2,C1:E1,0)) will return the reading 6.1 Changing the triple inputs to: July, Sunny, 3 will yield in J2: 15.2 Adapt the ranges to suit ------ ---- -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:363 Subscribers:64 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match, Index, If
Ok I'll explain alittle better what I'm trying to do. OK on the first sheet
there is a bunch of questions being asked to the user, What Month it is ( B2-B7). The next question is What type of day it is Cloudy or Sunny (C2-C7). These variable repeat because of the different months. The next question they have is the time of the day (D1 -AA1). Now based on these variables I want a temperature that corresponds with these variables to pop up into another box. Now this temperature changes with the different options the user picks. So say he picks october, cloudy, and 3 am the value 6.1 will come up in this box. This is a portion of the data set 1 2 3 4 5 April Sunny 3.7 3.2 2.8 2.4 1.6 April Cloudy 4.1 3.7 3.2 2.6 2 July Sunny 16.3 15.7 15.2 14.6 14.1 July Cloudy 18.6 18.2 17.5 16.2 15.7 October Sunny 6.9 6.1 5.7 5.1 4.5 October Cloudy 7.2 6.7 6.1 5.8 5.6 "Shane Devenshire" wrote: Hi, I don't understand you question. If you want to copy the formula down and use other values rather than C1, C2 and C3 then you could put the three values in three empty columns (say C1:E1) and then each new combination below them then copy the formula down =SUMPRODUCT(--(A$2:A$16=C1),--(B$2:B$16=D1),--($F$1:$T$1=E1) Note: I have moved the last range so it doesn't overlap the C1:E1 range. I don't know where your ranges are actually located. If this helps please click the Yes button. Cheers, Shane Devenshire "Shortstopper00" wrote: Hey, I used the code was helpful, but where do I index the values that I want come up. after the 3 variables are picked by the user "Shane Devenshire" wrote: Hi, Here is the basic idea: =SUMPRODUCT(--(A$2:A$16=C1),--(B$2:B$16=C2),--($D$1:$R$1=C3) In this example column A contains date, B contains Cloudy/Sunny, and range D1:R1 contains your third item. The values you want to count for each of these are in C1, C2, and C3. You could hard code these, ie. instead of C1 you could type "March". If this helps, please click the Yes button Cheers, Shane Devenshire "Shortstopper00" wrote: I have a data set that is user based. The answer that i want to bring up is dependant on 3 variables. The first is the month it is (column), second is the type of day sunny or cloudy (cloumn) and the third is the time of day which is in a row. If anyone could help me with a code taht could be used it would be greatly appreciated. 1 2 3 April Sunny 3.7 3.2 2.8 April Cloudy 4.1 3.7 3.2 July Sunny 16.3 15.7 15.2 July Cloudy 18.6 18.2 17.5 October Sunny 6.9 6.1 5.7 October Cloudy 7.2 6.7 6.1 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match, Index, If
Assuming the time of day is entered as an integer representing the hour.
That's what it looks like in your sample data. B10 = lookup month C10 = type of day (cloudy, sunny) D10 = hour of day =SUMPRODUCT(--(B2:B7=B10),--(C2:C7=C10),INDEX(D2:AA7,,D10)) -- Biff Microsoft Excel MVP "Shortstopper00" wrote in message ... Ok I'll explain alittle better what I'm trying to do. OK on the first sheet there is a bunch of questions being asked to the user, What Month it is ( B2-B7). The next question is What type of day it is Cloudy or Sunny (C2-C7). These variable repeat because of the different months. The next question they have is the time of the day (D1 -AA1). Now based on these variables I want a temperature that corresponds with these variables to pop up into another box. Now this temperature changes with the different options the user picks. So say he picks october, cloudy, and 3 am the value 6.1 will come up in this box. This is a portion of the data set 1 2 3 4 5 April Sunny 3.7 3.2 2.8 2.4 1.6 April Cloudy 4.1 3.7 3.2 2.6 2 July Sunny 16.3 15.7 15.2 14.6 14.1 July Cloudy 18.6 18.2 17.5 16.2 15.7 October Sunny 6.9 6.1 5.7 5.1 4.5 October Cloudy 7.2 6.7 6.1 5.8 5.6 "Shane Devenshire" wrote: Hi, I don't understand you question. If you want to copy the formula down and use other values rather than C1, C2 and C3 then you could put the three values in three empty columns (say C1:E1) and then each new combination below them then copy the formula down =SUMPRODUCT(--(A$2:A$16=C1),--(B$2:B$16=D1),--($F$1:$T$1=E1) Note: I have moved the last range so it doesn't overlap the C1:E1 range. I don't know where your ranges are actually located. If this helps please click the Yes button. Cheers, Shane Devenshire "Shortstopper00" wrote: Hey, I used the code was helpful, but where do I index the values that I want come up. after the 3 variables are picked by the user "Shane Devenshire" wrote: Hi, Here is the basic idea: =SUMPRODUCT(--(A$2:A$16=C1),--(B$2:B$16=C2),--($D$1:$R$1=C3) In this example column A contains date, B contains Cloudy/Sunny, and range D1:R1 contains your third item. The values you want to count for each of these are in C1, C2, and C3. You could hard code these, ie. instead of C1 you could type "March". If this helps, please click the Yes button Cheers, Shane Devenshire "Shortstopper00" wrote: I have a data set that is user based. The answer that i want to bring up is dependant on 3 variables. The first is the month it is (column), second is the type of day sunny or cloudy (cloumn) and the third is the time of day which is in a row. If anyone could help me with a code taht could be used it would be greatly appreciated. 1 2 3 April Sunny 3.7 3.2 2.8 April Cloudy 4.1 3.7 3.2 July Sunny 16.3 15.7 15.2 July Cloudy 18.6 18.2 17.5 October Sunny 6.9 6.1 5.7 October Cloudy 7.2 6.7 6.1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions |