BI REPORT

[SHWETA GOEL]

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

 

[Jacob Skaria]

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")

 

[SHWETA GOEL]

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.............

 

[SHWETA GOEL]

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]

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]

If Column C2 to C5 contains rates..

F2 =RANK(C2,$C$2:$C$5,1)

Drag the formula down upto F5

 

[SHWETA GOEL]

U R NOT GETTING ME
COLUMNS ARE MATERIAL , VENDOR AND AVG PRICE
RATING I HAV ETO CALCULATE AND DISPLAY

 

[Jacob Skaria]

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

 

[SHWETA GOEL]

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]

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

 

[SHWETA GOEL]

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]

Would suggest to close this post if it has helped and post a different one
under Excel Programming....(which helps others in future)