| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
BI REPORT
Hi,
I have a requirement , I have posted thread but not getting solution and its urgent so I am sending mail... If u can help me then I will be really thankful to you. I want to develop a report "Vendor evaluation" .. I have keyfigure invoice amount and invoice quantity... and characteristic Material and vendor then have to calculate wtd avg price by formula( invoice amount/invoice quantity). Now i got one more column wtd avg price... Report layout is material vendor inv value invoice quan wtd avg price price rating M1 V1 45 15 3 V2 40 15 2.6 40 V3 60 12 5 v4 80 12 6.6 As you see above.. for Material M1, four vendors are there , and v2 is giving material at lowest price so his rating is 40 v1 is second lowest so his rating will be calculated by formula Weightage ofFirst Lowest Vendor(i.e V2) - (Avg.Price of Second Vendor(v1) €“ Avg. price of First Vendor) / Avg. Price of first vendor ) *100 How to implement this scenerio... pls help ... REGARDS |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
BI REPORT
Got confused with the weightage formula you are using.....
Suppose.. B2 to B5 contains vendor v1 to v4 C 2 to C5 contains the rates Try this formula in F2. You need to replace "CALC" with the weightage formula... =IF(RANK(C2,$C$2:$C$5,1)=1,C2,"CALC") -- If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: Hi, I have a requirement , I have posted thread but not getting solution and its urgent so I am sending mail... If u can help me then I will be really thankful to you. I want to develop a report "Vendor evaluation" .. I have keyfigure invoice amount and invoice quantity... and characteristic Material and vendor then have to calculate wtd avg price by formula( invoice amount/invoice quantity). Now i got one more column wtd avg price... Report layout is material vendor inv value invoice quan wtd avg price price rating M1 V1 45 15 3 V2 40 15 2.6 40 V3 60 12 5 v4 80 12 6.6 As you see above.. for Material M1, four vendors are there , and v2 is giving material at lowest price so his rating is 40 v1 is second lowest so his rating will be calculated by formula Weightage ofFirst Lowest Vendor(i.e V2) - (Avg.Price of Second Vendor(v1) €“ Avg. price of First Vendor) / Avg. Price of first vendor ) *100 How to implement this scenerio... pls help ... REGARDS |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
BI REPORT
Actually the thing is , i have to calculate wtd price by dividing invoice
value by invoice qty for all vendors for a particular material... then the vendor having lowest wtd avg price then rating is 1 and if second lowest price then 2 like this............. "Jacob Skaria" wrote: Got confused with the weightage formula you are using..... Suppose.. B2 to B5 contains vendor v1 to v4 C 2 to C5 contains the rates Try this formula in F2. You need to replace "CALC" with the weightage formula... =IF(RANK(C2,$C$2:$C$5,1)=1,C2,"CALC") -- If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: Hi, I have a requirement , I have posted thread but not getting solution and its urgent so I am sending mail... If u can help me then I will be really thankful to you. I want to develop a report "Vendor evaluation" .. I have keyfigure invoice amount and invoice quantity... and characteristic Material and vendor then have to calculate wtd avg price by formula( invoice amount/invoice quantity). Now i got one more column wtd avg price... Report layout is material vendor inv value invoice quan wtd avg price price rating M1 V1 45 15 3 V2 40 15 2.6 40 V3 60 12 5 v4 80 12 6.6 As you see above.. for Material M1, four vendors are there , and v2 is giving material at lowest price so his rating is 40 v1 is second lowest so his rating will be calculated by formula Weightage ofFirst Lowest Vendor(i.e V2) - (Avg.Price of Second Vendor(v1) €“ Avg. price of First Vendor) / Avg. Price of first vendor ) *100 How to implement this scenerio... pls help ... REGARDS |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
BI REPORT
actually i have to calculate wtd avg price by dividing invoice val by invoice
qty for all vendors for a particular material . Then vendor having lowest avg price will be rating 1 , second lowest 2 and so on... "Jacob Skaria" wrote: Got confused with the weightage formula you are using..... Suppose.. B2 to B5 contains vendor v1 to v4 C 2 to C5 contains the rates Try this formula in F2. You need to replace "CALC" with the weightage formula... =IF(RANK(C2,$C$2:$C$5,1)=1,C2,"CALC") -- If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: Hi, I have a requirement , I have posted thread but not getting solution and its urgent so I am sending mail... If u can help me then I will be really thankful to you. I want to develop a report "Vendor evaluation" .. I have keyfigure invoice amount and invoice quantity... and characteristic Material and vendor then have to calculate wtd avg price by formula( invoice amount/invoice quantity). Now i got one more column wtd avg price... Report layout is material vendor inv value invoice quan wtd avg price price rating M1 V1 45 15 3 V2 40 15 2.6 40 V3 60 12 5 v4 80 12 6.6 As you see above.. for Material M1, four vendors are there , and v2 is giving material at lowest price so his rating is 40 v1 is second lowest so his rating will be calculated by formula Weightage ofFirst Lowest Vendor(i.e V2) - (Avg.Price of Second Vendor(v1) €“ Avg. price of First Vendor) / Avg. Price of first vendor ) *100 How to implement this scenerio... pls help ... REGARDS |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
BI REPORT
The Rank function is enough. Column C2 to C5 contains rates..
=RANK(C2,$C$2:$C$5,1) If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: actually i have to calculate wtd avg price by dividing invoice val by invoice qty for all vendors for a particular material . Then vendor having lowest avg price will be rating 1 , second lowest 2 and so on... "Jacob Skaria" wrote: Got confused with the weightage formula you are using..... Suppose.. B2 to B5 contains vendor v1 to v4 C 2 to C5 contains the rates Try this formula in F2. You need to replace "CALC" with the weightage formula... =IF(RANK(C2,$C$2:$C$5,1)=1,C2,"CALC") -- If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: Hi, I have a requirement , I have posted thread but not getting solution and its urgent so I am sending mail... If u can help me then I will be really thankful to you. I want to develop a report "Vendor evaluation" .. I have keyfigure invoice amount and invoice quantity... and characteristic Material and vendor then have to calculate wtd avg price by formula( invoice amount/invoice quantity). Now i got one more column wtd avg price... Report layout is material vendor inv value invoice quan wtd avg price price rating M1 V1 45 15 3 V2 40 15 2.6 40 V3 60 12 5 v4 80 12 6.6 As you see above.. for Material M1, four vendors are there , and v2 is giving material at lowest price so his rating is 40 v1 is second lowest so his rating will be calculated by formula Weightage ofFirst Lowest Vendor(i.e V2) - (Avg.Price of Second Vendor(v1) €“ Avg. price of First Vendor) / Avg. Price of first vendor ) *100 How to implement this scenerio... pls help ... REGARDS |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
BI REPORT
If Column C2 to C5 contains rates..
F2 =RANK(C2,$C$2:$C$5,1) Drag the formula down upto F5 -- If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: Hi, I have a requirement , I have posted thread but not getting solution and its urgent so I am sending mail... If u can help me then I will be really thankful to you. I want to develop a report "Vendor evaluation" .. I have keyfigure invoice amount and invoice quantity... and characteristic Material and vendor then have to calculate wtd avg price by formula( invoice amount/invoice quantity). Now i got one more column wtd avg price... Report layout is material vendor inv value invoice quan wtd avg price price rating M1 V1 45 15 3 V2 40 15 2.6 40 V3 60 12 5 v4 80 12 6.6 As you see above.. for Material M1, four vendors are there , and v2 is giving material at lowest price so his rating is 40 v1 is second lowest so his rating will be calculated by formula Weightage ofFirst Lowest Vendor(i.e V2) - (Avg.Price of Second Vendor(v1) €“ Avg. price of First Vendor) / Avg. Price of first vendor ) *100 How to implement this scenerio... pls help ... REGARDS |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
BI REPORT
U R NOT GETTING ME
COLUMNS ARE MATERIAL , VENDOR AND AVG PRICE RATING I HAV ETO CALCULATE AND DISPLAY "Jacob Skaria" wrote: If Column C2 to C5 contains rates.. F2 =RANK(C2,$C$2:$C$5,1) Drag the formula down upto F5 -- If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: Hi, I have a requirement , I have posted thread but not getting solution and its urgent so I am sending mail... If u can help me then I will be really thankful to you. I want to develop a report "Vendor evaluation" .. I have keyfigure invoice amount and invoice quantity... and characteristic Material and vendor then have to calculate wtd avg price by formula( invoice amount/invoice quantity). Now i got one more column wtd avg price... Report layout is material vendor inv value invoice quan wtd avg price price rating M1 V1 45 15 3 V2 40 15 2.6 40 V3 60 12 5 v4 80 12 6.6 As you see above.. for Material M1, four vendors are there , and v2 is giving material at lowest price so his rating is 40 v1 is second lowest so his rating will be calculated by formula Weightage ofFirst Lowest Vendor(i.e V2) - (Avg.Price of Second Vendor(v1) €“ Avg. price of First Vendor) / Avg. Price of first vendor ) *100 How to implement this scenerio... pls help ... REGARDS |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
BI REPORT
Sorry..
B2 to B5 is vendor C2 to C5 is invoice value D2 to d5 is quanti E2 to e5 is avg, F2 = RANK(E2,$E$2:$E$5,1) If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: U R NOT GETTING ME COLUMNS ARE MATERIAL , VENDOR AND AVG PRICE RATING I HAV ETO CALCULATE AND DISPLAY "Jacob Skaria" wrote: If Column C2 to C5 contains rates.. F2 =RANK(C2,$C$2:$C$5,1) Drag the formula down upto F5 -- If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: Hi, I have a requirement , I have posted thread but not getting solution and its urgent so I am sending mail... If u can help me then I will be really thankful to you. I want to develop a report "Vendor evaluation" .. I have keyfigure invoice amount and invoice quantity... and characteristic Material and vendor then have to calculate wtd avg price by formula( invoice amount/invoice quantity). Now i got one more column wtd avg price... Report layout is material vendor inv value invoice quan wtd avg price price rating M1 V1 45 15 3 V2 40 15 2.6 40 V3 60 12 5 v4 80 12 6.6 As you see above.. for Material M1, four vendors are there , and v2 is giving material at lowest price so his rating is 40 v1 is second lowest so his rating will be calculated by formula Weightage ofFirst Lowest Vendor(i.e V2) - (Avg.Price of Second Vendor(v1) €“ Avg. price of First Vendor) / Avg. Price of first vendor ) *100 How to implement this scenerio... pls help ... REGARDS |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
BI REPORT
Thanks Jacob,
But prob is report is dynamic means for m1 , 4 vendors are there then have to display rating for 4 vendors then for m2 (material), 5 vendors are there then have to dispaly rating for those 5 vendors ..and number of vendors and materials are not fixed it is depend on input date.... format is Material vendor price rating M1 v1 40 3 v2 36 2 v3 20 1 ------------------------------------ M2 v1 45 2 v5 67 3 v6 36 1 and so on "Jacob Skaria" wrote: Sorry.. B2 to B5 is vendor C2 to C5 is invoice value D2 to d5 is quanti E2 to e5 is avg, F2 = RANK(E2,$E$2:$E$5,1) If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: U R NOT GETTING ME COLUMNS ARE MATERIAL , VENDOR AND AVG PRICE RATING I HAV ETO CALCULATE AND DISPLAY "Jacob Skaria" wrote: If Column C2 to C5 contains rates.. F2 =RANK(C2,$C$2:$C$5,1) Drag the formula down upto F5 -- If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: Hi, I have a requirement , I have posted thread but not getting solution and its urgent so I am sending mail... If u can help me then I will be really thankful to you. I want to develop a report "Vendor evaluation" .. I have keyfigure invoice amount and invoice quantity... and characteristic Material and vendor then have to calculate wtd avg price by formula( invoice amount/invoice quantity). Now i got one more column wtd avg price... Report layout is material vendor inv value invoice quan wtd avg price price rating M1 V1 45 15 3 V2 40 15 2.6 40 V3 60 12 5 v4 80 12 6.6 As you see above.. for Material M1, four vendors are there , and v2 is giving material at lowest price so his rating is 40 v1 is second lowest so his rating will be calculated by formula Weightage ofFirst Lowest Vendor(i.e V2) - (Avg.Price of Second Vendor(v1) €“ Avg. price of First Vendor) / Avg. Price of first vendor ) *100 How to implement this scenerio... pls help ... REGARDS |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
BI REPORT
You will have to use a macro to do this///
A small question...In the below table i dont see the quantity and average columns//// If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: Thanks Jacob, But prob is report is dynamic means for m1 , 4 vendors are there then have to display rating for 4 vendors then for m2 (material), 5 vendors are there then have to dispaly rating for those 5 vendors ..and number of vendors and materials are not fixed it is depend on input date.... format is Material vendor price rating M1 v1 40 3 v2 36 2 v3 20 1 ------------------------------------ M2 v1 45 2 v5 67 3 v6 36 1 and so on "Jacob Skaria" wrote: Sorry.. B2 to B5 is vendor C2 to C5 is invoice value D2 to d5 is quanti E2 to e5 is avg, F2 = RANK(E2,$E$2:$E$5,1) If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: U R NOT GETTING ME COLUMNS ARE MATERIAL , VENDOR AND AVG PRICE RATING I HAV ETO CALCULATE AND DISPLAY "Jacob Skaria" wrote: If Column C2 to C5 contains rates.. F2 =RANK(C2,$C$2:$C$5,1) Drag the formula down upto F5 -- If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: Hi, I have a requirement , I have posted thread but not getting solution and its urgent so I am sending mail... If u can help me then I will be really thankful to you. I want to develop a report "Vendor evaluation" .. I have keyfigure invoice amount and invoice quantity... and characteristic Material and vendor then have to calculate wtd avg price by formula( invoice amount/invoice quantity). Now i got one more column wtd avg price... Report layout is material vendor inv value invoice quan wtd avg price price rating M1 V1 45 15 3 V2 40 15 2.6 40 V3 60 12 5 v4 80 12 6.6 As you see above.. for Material M1, four vendors are there , and v2 is giving material at lowest price so his rating is 40 v1 is second lowest so his rating will be calculated by formula Weightage ofFirst Lowest Vendor(i.e V2) - (Avg.Price of Second Vendor(v1) €“ Avg. price of First Vendor) / Avg. Price of first vendor ) *100 How to implement this scenerio... pls help ... REGARDS |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
BI REPORT
Those columns are there but i removed just for convienence...
can u pls tell me how to write macro ...its really very urgent... Thanks in advance "Jacob Skaria" wrote: You will have to use a macro to do this/// A small question...In the below table i dont see the quantity and average columns//// If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: Thanks Jacob, But prob is report is dynamic means for m1 , 4 vendors are there then have to display rating for 4 vendors then for m2 (material), 5 vendors are there then have to dispaly rating for those 5 vendors ..and number of vendors and materials are not fixed it is depend on input date.... format is Material vendor price rating M1 v1 40 3 v2 36 2 v3 20 1 ------------------------------------ M2 v1 45 2 v5 67 3 v6 36 1 and so on "Jacob Skaria" wrote: Sorry.. B2 to B5 is vendor C2 to C5 is invoice value D2 to d5 is quanti E2 to e5 is avg, F2 = RANK(E2,$E$2:$E$5,1) If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: U R NOT GETTING ME COLUMNS ARE MATERIAL , VENDOR AND AVG PRICE RATING I HAV ETO CALCULATE AND DISPLAY "Jacob Skaria" wrote: If Column C2 to C5 contains rates.. F2 =RANK(C2,$C$2:$C$5,1) Drag the formula down upto F5 -- If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: Hi, I have a requirement , I have posted thread but not getting solution and its urgent so I am sending mail... If u can help me then I will be really thankful to you. I want to develop a report "Vendor evaluation" .. I have keyfigure invoice amount and invoice quantity... and characteristic Material and vendor then have to calculate wtd avg price by formula( invoice amount/invoice quantity). Now i got one more column wtd avg price... Report layout is material vendor inv value invoice quan wtd avg price price rating M1 V1 45 15 3 V2 40 15 2.6 40 V3 60 12 5 v4 80 12 6.6 As you see above.. for Material M1, four vendors are there , and v2 is giving material at lowest price so his rating is 40 v1 is second lowest so his rating will be calculated by formula Weightage ofFirst Lowest Vendor(i.e V2) - (Avg.Price of Second Vendor(v1) €“ Avg. price of First Vendor) / Avg. Price of first vendor ) *100 How to implement this scenerio... pls help ... REGARDS |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
BI REPORT
Would suggest to close this post if it has helped and post a different one
under Excel Programming....(which helps others in future) -- If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: Those columns are there but i removed just for convienence... can u pls tell me how to write macro ...its really very urgent... Thanks in advance "Jacob Skaria" wrote: You will have to use a macro to do this/// A small question...In the below table i dont see the quantity and average columns//// If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: Thanks Jacob, But prob is report is dynamic means for m1 , 4 vendors are there then have to display rating for 4 vendors then for m2 (material), 5 vendors are there then have to dispaly rating for those 5 vendors ..and number of vendors and materials are not fixed it is depend on input date.... format is Material vendor price rating M1 v1 40 3 v2 36 2 v3 20 1 ------------------------------------ M2 v1 45 2 v5 67 3 v6 36 1 and so on "Jacob Skaria" wrote: Sorry.. B2 to B5 is vendor C2 to C5 is invoice value D2 to d5 is quanti E2 to e5 is avg, F2 = RANK(E2,$E$2:$E$5,1) If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: U R NOT GETTING ME COLUMNS ARE MATERIAL , VENDOR AND AVG PRICE RATING I HAV ETO CALCULATE AND DISPLAY "Jacob Skaria" wrote: If Column C2 to C5 contains rates.. F2 =RANK(C2,$C$2:$C$5,1) Drag the formula down upto F5 -- If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: Hi, I have a requirement , I have posted thread but not getting solution and its urgent so I am sending mail... If u can help me then I will be really thankful to you. I want to develop a report "Vendor evaluation" .. I have keyfigure invoice amount and invoice quantity... and characteristic Material and vendor then have to calculate wtd avg price by formula( invoice amount/invoice quantity). Now i got one more column wtd avg price... Report layout is material vendor inv value invoice quan wtd avg price price rating M1 V1 45 15 3 V2 40 15 2.6 40 V3 60 12 5 v4 80 12 6.6 As you see above.. for Material M1, four vendors are there , and v2 is giving material at lowest price so his rating is 40 v1 is second lowest so his rating will be calculated by formula Weightage ofFirst Lowest Vendor(i.e V2) - (Avg.Price of Second Vendor(v1) €“ Avg. price of First Vendor) / Avg. Price of first vendor ) *100 How to implement this scenerio... pls help ... REGARDS |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Getting missing data from one report into another report... | Excel Discussion (Misc queries) | |||
| Print Report W/Sub Report | Excel Discussion (Misc queries) | |||
| Automate Excel report to place certain data into existing report? | Excel Worksheet Functions | |||
| Header in Report Manager Report | Excel Discussion (Misc queries) | |||
| =(IF(ISTEXT('Data Report'!$L2:$L4),'Data Report'!N3,J11)) Response | Excel Discussion (Misc queries) | |||
Linear Mode
