| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
i am in search of Formula / Function in EXCEL-2003
Dear Sir,
I am in search of a formula to fill up the cells of "Price per each" column ? Data Available Farmer Product Price Per Each Farmer Product Price Per Each Tom Apple Tom Apple 10 Harry Apple Tom Mango 15 Harry Banana Tom Banana 5 Tom Apple Harry Apple 20 Harry Mango Harry Mango 30 Harry Apple Harry Banana 7 Tom Mango Harry Mango Tom Banana Tom Mango Tom Banana Tom Apple Harry Apple Tom Mango Regards, Nimish |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
Hi Nimish
Say if your data table is in A1:C7; Try it in C9 C9 =INDEX($A$1:$C$7, MATCH(1,(($A$1:$A$7=A9)*($B$1:$B$7=B9)),0), MATCH("Cost",$A$1:$C$1,)) it is an array formula. you have to use ctrl+shift+enter not just enter A9= Name B9 = Product with regards Sridhar "Nimish Shah" wrote: Dear Sir, I am in search of a formula to fill up the cells of "Price per each" column ? Data Available Farmer Product Price Per Each Farmer Product Price Per Each Tom Apple Tom Apple 10 Harry Apple Tom Mango 15 Harry Banana Tom Banana 5 Tom Apple Harry Apple 20 Harry Mango Harry Mango 30 Harry Apple Harry Banana 7 Tom Mango Harry Mango Tom Banana Tom Mango Tom Banana Tom Apple Harry Apple Tom Mango Regards, Nimish |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
Hi Sridhar,
I have tried the formula but not working, showing as #N/A. I could not understand: match("cost",$a$1:$c$1,)). I have never used the ARRAY formula. Please help. Regards, Nimish "yshridhar" wrote: Hi Nimish Say if your data table is in A1:C7; Try it in C9 C9 =INDEX($A$1:$C$7, MATCH(1,(($A$1:$A$7=A9)*($B$1:$B$7=B9)),0), MATCH("Cost",$A$1:$C$1,)) it is an array formula. you have to use ctrl+shift+enter not just enter A9= Name B9 = Product with regards Sridhar "Nimish Shah" wrote: Dear Sir, I am in search of a formula to fill up the cells of "Price per each" column ? Data Available Farmer Product Price Per Each Farmer Product Price Per Each Tom Apple Tom Apple 10 Harry Apple Tom Mango 15 Harry Banana Tom Banana 5 Tom Apple Harry Apple 20 Harry Mango Harry Mango 30 Harry Apple Harry Banana 7 Tom Mango Harry Mango Tom Banana Tom Mango Tom Banana Tom Apple Harry Apple Tom Mango Regards, Nimish |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
Array formula has to be enetered by ctrl+shift+enter. Not just by pressing
enter. a1 = name b1 = product c1 = cost change the column heads according to your data. You have to enter the formula by pressing ctrl+shift+enter with regards Sridhar "Nimish Shah" wrote: Hi Sridhar, I have tried the formula but not working, showing as #N/A. I could not understand: match("cost",$a$1:$c$1,)). I have never used the ARRAY formula. Please help. Regards, Nimish "yshridhar" wrote: Hi Nimish Say if your data table is in A1:C7; Try it in C9 C9 =INDEX($A$1:$C$7, MATCH(1,(($A$1:$A$7=A9)*($B$1:$B$7=B9)),0), MATCH("Cost",$A$1:$C$1,)) it is an array formula. you have to use ctrl+shift+enter not just enter A9= Name B9 = Product with regards Sridhar "Nimish Shah" wrote: Dear Sir, I am in search of a formula to fill up the cells of "Price per each" column ? Data Available Farmer Product Price Per Each Farmer Product Price Per Each Tom Apple Tom Apple 10 Harry Apple Tom Mango 15 Harry Banana Tom Banana 5 Tom Apple Harry Apple 20 Harry Mango Harry Mango 30 Harry Apple Harry Banana 7 Tom Mango Harry Mango Tom Banana Tom Mango Tom Banana Tom Apple Harry Apple Tom Mango Regards, Nimish |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
The following is the data
Name Product Cost Tom Banana 10 Tom Mango 20 Tom Apple 30 Harry Banana 40 Harry Mango 50 Harry Apple 60 A9 = Tom B9 = Apple C9 =INDEX($A$1:$C$7, MATCH(1,(($A$1:$A$7=A9)*($B$1:$B$7=B9)),0),MATCH(" Cost",$A$1:$C$1,)) change the column heads and range according to your data. You can find help about array formula in excel. copy down the formula in C9 and press ctrl+shift+enter. with regards Sridhar "yshridhar" wrote: Array formula has to be enetered by ctrl+shift+enter. Not just by pressing enter. a1 = name b1 = product c1 = cost change the column heads according to your data. You have to enter the formula by pressing ctrl+shift+enter with regards Sridhar "Nimish Shah" wrote: Hi Sridhar, I have tried the formula but not working, showing as #N/A. I could not understand: match("cost",$a$1:$c$1,)). I have never used the ARRAY formula. Please help. Regards, Nimish "yshridhar" wrote: Hi Nimish Say if your data table is in A1:C7; Try it in C9 C9 =INDEX($A$1:$C$7, MATCH(1,(($A$1:$A$7=A9)*($B$1:$B$7=B9)),0), MATCH("Cost",$A$1:$C$1,)) it is an array formula. you have to use ctrl+shift+enter not just enter A9= Name B9 = Product with regards Sridhar "Nimish Shah" wrote: Dear Sir, I am in search of a formula to fill up the cells of "Price per each" column ? Data Available Farmer Product Price Per Each Farmer Product Price Per Each Tom Apple Tom Apple 10 Harry Apple Tom Mango 15 Harry Banana Tom Banana 5 Tom Apple Harry Apple 20 Harry Mango Harry Mango 30 Harry Apple Harry Banana 7 Tom Mango Harry Mango Tom Banana Tom Mango Tom Banana Tom Apple Harry Apple Tom Mango Regards, Nimish |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
Sridhar,
Thank you so much. It woorks very well. i had forgot to name C1 as cost. i did do the ctrl+shift+enter. Regards, Nimish "yshridhar" wrote: Array formula has to be enetered by ctrl+shift+enter. Not just by pressing enter. a1 = name b1 = product c1 = cost change the column heads according to your data. You have to enter the formula by pressing ctrl+shift+enter with regards Sridhar "Nimish Shah" wrote: Hi Sridhar, I have tried the formula but not working, showing as #N/A. I could not understand: match("cost",$a$1:$c$1,)). I have never used the ARRAY formula. Please help. Regards, Nimish "yshridhar" wrote: Hi Nimish Say if your data table is in A1:C7; Try it in C9 C9 =INDEX($A$1:$C$7, MATCH(1,(($A$1:$A$7=A9)*($B$1:$B$7=B9)),0), MATCH("Cost",$A$1:$C$1,)) it is an array formula. you have to use ctrl+shift+enter not just enter A9= Name B9 = Product with regards Sridhar "Nimish Shah" wrote: Dear Sir, I am in search of a formula to fill up the cells of "Price per each" column ? Data Available Farmer Product Price Per Each Farmer Product Price Per Each Tom Apple Tom Apple 10 Harry Apple Tom Mango 15 Harry Banana Tom Banana 5 Tom Apple Harry Apple 20 Harry Mango Harry Mango 30 Harry Apple Harry Banana 7 Tom Mango Harry Mango Tom Banana Tom Mango Tom Banana Tom Apple Harry Apple Tom Mango Regards, Nimish |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
Thanks. It solved your problem. But the sumproduct version is simple than
the array formula. with regards Sridhar "Nimish Shah" wrote: Sridhar, Thank you so much. It woorks very well. i had forgot to name C1 as cost. i did do the ctrl+shift+enter. Regards, Nimish "yshridhar" wrote: Array formula has to be enetered by ctrl+shift+enter. Not just by pressing enter. a1 = name b1 = product c1 = cost change the column heads according to your data. You have to enter the formula by pressing ctrl+shift+enter with regards Sridhar "Nimish Shah" wrote: Hi Sridhar, I have tried the formula but not working, showing as #N/A. I could not understand: match("cost",$a$1:$c$1,)). I have never used the ARRAY formula. Please help. Regards, Nimish "yshridhar" wrote: Hi Nimish Say if your data table is in A1:C7; Try it in C9 C9 =INDEX($A$1:$C$7, MATCH(1,(($A$1:$A$7=A9)*($B$1:$B$7=B9)),0), MATCH("Cost",$A$1:$C$1,)) it is an array formula. you have to use ctrl+shift+enter not just enter A9= Name B9 = Product with regards Sridhar "Nimish Shah" wrote: Dear Sir, I am in search of a formula to fill up the cells of "Price per each" column ? Data Available Farmer Product Price Per Each Farmer Product Price Per Each Tom Apple Tom Apple 10 Harry Apple Tom Mango 15 Harry Banana Tom Banana 5 Tom Apple Harry Apple 20 Harry Mango Harry Mango 30 Harry Apple Harry Banana 7 Tom Mango Harry Mango Tom Banana Tom Mango Tom Banana Tom Apple Harry Apple Tom Mango Regards, Nimish |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
The following is your data
Name Product Cost Tom Banana 10 Tom Mango 20 Tom Apple 30 Harry Banana 40 Harry Mango 50 Harry Apple 60 a9 = Tom b9 = apple in c9=INDEX($A$1:$C$7, MATCH(1,(($A$1:$A$7=A9)*($B$1:$B$7=B9)),0),MATCH(" Cost",$A$1:$C$1,)) enter the formula in c9. Press F2 and then use ctrl(control key)+shift+enter. change the column heads accordingly. "cost" is the column head for the Price of your data. with regards sridhar "Nimish Shah" wrote: Hi Sridhar, I have tried the formula but not working, showing as #N/A. I could not understand: match("cost",$a$1:$c$1,)). I have never used the ARRAY formula. Please help. Regards, Nimish "yshridhar" wrote: Hi Nimish Say if your data table is in A1:C7; Try it in C9 C9 =INDEX($A$1:$C$7, MATCH(1,(($A$1:$A$7=A9)*($B$1:$B$7=B9)),0), MATCH("Cost",$A$1:$C$1,)) it is an array formula. you have to use ctrl+shift+enter not just enter A9= Name B9 = Product with regards Sridhar "Nimish Shah" wrote: Dear Sir, I am in search of a formula to fill up the cells of "Price per each" column ? Data Available Farmer Product Price Per Each Farmer Product Price Per Each Tom Apple Tom Apple 10 Harry Apple Tom Mango 15 Harry Banana Tom Banana 5 Tom Apple Harry Apple 20 Harry Mango Harry Mango 30 Harry Apple Harry Banana 7 Tom Mango Harry Mango Tom Banana Tom Mango Tom Banana Tom Apple Harry Apple Tom Mango Regards, Nimish |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
Nimish
You can try this simple formula C10 = SUMPRODUCT(--($A$2:$A$7=A10)*--($B$2:$B$7=B10),C2:C7) A10 = Tom B10 = Apple a2:a7 = farmer names b2:b7 = product names c2 : c7 = price With regards Sridhar "Nimish Shah" wrote: Hi Sridhar, I have tried the formula but not working, showing as #N/A. I could not understand: match("cost",$a$1:$c$1,)). I have never used the ARRAY formula. Please help. Regards, Nimish "yshridhar" wrote: Hi Nimish Say if your data table is in A1:C7; Try it in C9 C9 =INDEX($A$1:$C$7, MATCH(1,(($A$1:$A$7=A9)*($B$1:$B$7=B9)),0), MATCH("Cost",$A$1:$C$1,)) it is an array formula. you have to use ctrl+shift+enter not just enter A9= Name B9 = Product with regards Sridhar "Nimish Shah" wrote: Dear Sir, I am in search of a formula to fill up the cells of "Price per each" column ? Data Available Farmer Product Price Per Each Farmer Product Price Per Each Tom Apple Tom Apple 10 Harry Apple Tom Mango 15 Harry Banana Tom Banana 5 Tom Apple Harry Apple 20 Harry Mango Harry Mango 30 Harry Apple Harry Banana 7 Tom Mango Harry Mango Tom Banana Tom Mango Tom Banana Tom Apple Harry Apple Tom Mango Regards, Nimish |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
Hi Sridhar,
I am in search of a sot of reverse formula. Example is given below. Please help. Farmer COST-Apple COST-Banana COST-Mango Tom ??? ??? ??? Harry ??? ??? ??? Data Available Farmer Product COST Tom Apple 10 Harry Apple 20 Harry Banana 7 Tom Apple 10 Harry Mango 30 Harry Apple 20 Tom Mango 15 Harry Mango 30 Tom Banana 5 Tom Mango 15 Regards, Nimish "yshridhar" wrote: Nimish You can try this simple formula C10 = SUMPRODUCT(--($A$2:$A$7=A10)*--($B$2:$B$7=B10),C2:C7) A10 = Tom B10 = Apple a2:a7 = farmer names b2:b7 = product names c2 : c7 = price With regards Sridhar "Nimish Shah" wrote: Hi Sridhar, I have tried the formula but not working, showing as #N/A. I could not understand: match("cost",$a$1:$c$1,)). I have never used the ARRAY formula. Please help. Regards, Nimish "yshridhar" wrote: Hi Nimish Say if your data table is in A1:C7; Try it in C9 C9 =INDEX($A$1:$C$7, MATCH(1,(($A$1:$A$7=A9)*($B$1:$B$7=B9)),0), MATCH("Cost",$A$1:$C$1,)) it is an array formula. you have to use ctrl+shift+enter not just enter A9= Name B9 = Product with regards Sridhar "Nimish Shah" wrote: Dear Sir, I am in search of a formula to fill up the cells of "Price per each" column ? Data Available Farmer Product Price Per Each Farmer Product Price Per Each Tom Apple Tom Apple 10 Harry Apple Tom Mango 15 Harry Banana Tom Banana 5 Tom Apple Harry Apple 20 Harry Mango Harry Mango 30 Harry Apple Harry Banana 7 Tom Mango Harry Mango Tom Banana Tom Mango Tom Banana Tom Apple Harry Apple Tom Mango Regards, Nimish |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
Hi Nimish
I hope by this time you have sorted the problem. The same sumproduct will work. Try this b12=SUMPRODUCT(--($A$2:$A$7=$A12)*--($B$2:$B$7=B$11),$C$2:$C$7) a12 = Tom b11= apple (column heads "farmer- A, apple - B, Banana - C, Mango - D) i replaced cost-apple with apple a2:c7 is your data. Just copy down the formula with regards Sridhar "Nimish Shah" wrote: Hi Sridhar, I am in search of a sot of reverse formula. Example is given below. Please help. Farmer COST-Apple COST-Banana COST-Mango Tom ??? ??? ??? Harry ??? ??? ??? Data Available Farmer Product COST Tom Apple 10 Harry Apple 20 Harry Banana 7 Tom Apple 10 Harry Mango 30 Harry Apple 20 Tom Mango 15 Harry Mango 30 Tom Banana 5 Tom Mango 15 Regards, Nimish "yshridhar" wrote: Nimish You can try this simple formula C10 = SUMPRODUCT(--($A$2:$A$7=A10)*--($B$2:$B$7=B10),C2:C7) A10 = Tom B10 = Apple a2:a7 = farmer names b2:b7 = product names c2 : c7 = price With regards Sridhar "Nimish Shah" wrote: Hi Sridhar, I have tried the formula but not working, showing as #N/A. I could not understand: match("cost",$a$1:$c$1,)). I have never used the ARRAY formula. Please help. Regards, Nimish "yshridhar" wrote: Hi Nimish Say if your data table is in A1:C7; Try it in C9 C9 =INDEX($A$1:$C$7, MATCH(1,(($A$1:$A$7=A9)*($B$1:$B$7=B9)),0), MATCH("Cost",$A$1:$C$1,)) it is an array formula. you have to use ctrl+shift+enter not just enter A9= Name B9 = Product with regards Sridhar "Nimish Shah" wrote: Dear Sir, I am in search of a formula to fill up the cells of "Price per each" column ? Data Available Farmer Product Price Per Each Farmer Product Price Per Each Tom Apple Tom Apple 10 Harry Apple Tom Mango 15 Harry Banana Tom Banana 5 Tom Apple Harry Apple 20 Harry Mango Harry Mango 30 Harry Apple Harry Banana 7 Tom Mango Harry Mango Tom Banana Tom Mango Tom Banana Tom Apple Harry Apple Tom Mango Regards, Nimish |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Search Function | Excel Discussion (Misc queries) | |||
| Excel search function | Excel Discussion (Misc queries) | |||
| In Excel 2003 I must do a file search to fine my files | Excel Discussion (Misc queries) | |||
| search box in excel 2003? | Excel Worksheet Functions | |||
| Excel 2003 SP 1 does not search for files on a removable HDD | Excel Discussion (Misc queries) | |||
Hybrid Mode
