• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Excel Formula on Price

Hello All,

Can any one help on getting formula for the below requirement.

I have huge data list of products and price, i need formula to get Delta Price where i can apply in one shot for all the rows.

Each config product there will be Delta(N/Y) and i need the difference of the delta product price.

E.g:- 287 - 138 = 149
138 - 138 = 0.00
200 - 138 = 62
200 - 138 = 62
111 - 39 = 72

It will be really great if someone help on this.

Config Price​
Product Description​
Product Price​
Delta​
Delta Price​
380​
HP IDS UMA i3-1115G4 nWWAN TBT640G8BNBPC​
138.00​
N​
380​
Standard Packaging​
1.00​
N​
380​
OS Localization​
0.00​
N​
380​
FreeDOS​
0.00​
N​
380​
Integrated HD 720p DM Webcam​
2.00​
N​
380​
14 FHD AG LED UWVA 250 fHDC NWBZ bent​
37.00​
N​
380​
16GB (1x16GB) DDR4 3200​
39.00​
N​
380​
256GB PCIe NVMe Value SSD​
19.00​
N​
380​
HP IDS DSC i5-1135G7 nWWAN TBT640G8BNBPC​
287.00​
Y​
149.00​
380​
HP IDS UMA i3-1115G4 fWWAN TBT640G8BNBPC​
138.00​
Y​
0.00​
380​
HP IDS UMA i5-1135G7 fWWAN TBT640G8BNBPC​
200.00​
Y​
62.00​
380​
HP IDS UMA i5-1135G7 nWWAN TBT640G8BNBPC​
200.00​
Y​
62.00​
380​
32GB (2x16GB) DDR4 3200​
111.00​
Y​
-27.00​
 

Attachments

  • Delta Price Formula.xlsx
    17.7 KB · Views: 6
Hello Vletm,

Thanks for your reply.

the logic is which ever upgrade or downgrade product is there for that we have to calculate the price difference.

Eg if on product description we have 32Gb and on Delta column is N, then we need to calculate the price difference for product 16Gb and Delta column which has Y.

if on product description we have 128Gb and on Delta column is N, then again we need to calculate the price difference for product 16Gb and Delta column which has Y.

So on E14 = C14 - C8 = 72

i know it is tricky but if i get any solution it will be very help full.

Regards,
Anantha
 
Anantha Krishna
As written in #2
Is there clear rules?
Your given rules gives 72 and -27 for Cell E14.
... Do Your rules works - if somewhere is 123Gb then ... How to know, why not try to solve difference with FreeDOS?
Your i know it is tricky but if i get any solution it will be very help full.
give too much freedom for me ( any solution ).
 
In E2, formula copied down :

=IF(D2="Y",C2-VLOOKUP("*"&TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",50)),50)),B$1:C1,2,0),"")

78143
 
Last edited:
Formula result put in Column F,

In F2, formula copied down :

=IF(D2="Y",C2-VLOOKUP("*"&TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",50)),50)),B$1:C1,2,0),"")

View attachment 78088
Hello
bosco_yip,

I have tried above formula, but some of the lines what is happening is price is calculating from DColumn which has "Y" to DColumn which has "Y".

But i need price should calculate by matching description from BColumn and DColumn which has "N"

I modified your formula as below but it is not working.

=IF(D10="Y",C10-VLOOKUP(D:D="N"&"*"&TRIM(RIGHT(SUBSTITUTE(B10," ",REPT(" ",50)),100)),B$1:C10,2,0),"")

Can you please help on this?

And also there will be different config ID for each set of products, hence is it possible to calculate price only for the particular Config ID which has on Acolumn?

Attached excel file for your reference, and marked in red for few price which is not correct by formula.

Regards,
Anantha
 

Attachments

  • Formula for Delta Price.xlsx
    585.7 KB · Views: 6
Last edited by a moderator:
Anantha Krishna
One sample based Your is it possible to calculate price only for the particular Config ID which has on Acolumn?
 

Attachments

  • Formula for Delta Price.xlsb
    280.6 KB · Views: 3
Anantha Krishna
One sample based Your is it possible to calculate price only for the particular Config ID which has on Acolumn?
Hello
vletm,

Thanks for your response, but the file which you provided on the price is not coming correct as per need.

The thing is Config ID 34257192 from Acolumn i have 80 Products on that for the product description which has Delta as "Y" on DColumn has to calculate the price from same config which has "N" on Delta for upgrade or downgrade product.

Eg:- for Config ID 34257192, Product "32GB (2x16GB) DDR4 2666" which has Delta "Y" minus Product "8GB (1x8GB) DDR4 2666 DIMM" Which has Delta "N" , 124.46 - 19.46 = 105

Thank you in Advance

Regards,
Anantha Krishna
 
Anantha Krishna
Will You change those rules every time?
Did You read that my *One sample based ...' -line?
Did You use there word only?
Why do You write something about B-column data?
I can someway take care Your the newest ... Y/N ... with my the last sample.

Do You remember that You've written in #3 reply
i know it is tricky but if i get any solution it will be very help full.
You've already gotten many solutions.
 

Attachments

  • Formula for Delta Price.xlsb
    287.3 KB · Views: 2
Back
Top