multiplying 3 different rate for different nos of products

harsharmani

New Member
Joined
Oct 29, 2017
Messages
7
[TABLE="class: cms_table, width: 780"]
<tbody>[TR]
[TD][TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD]product[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bioprine[/TD]
[TD][TABLE="class: cms_table, width: 118"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 118"]Rs 10 for 5 product[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 73"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 73"]rs 20 for 10 product[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 126"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 126"]rs 30 for 15 product[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 153"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 153"]no of product required[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 64"]rate[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 182"]
<tbody>[TR]
[TD]customer will enter desired no of product in D column and rate shd reflect in E coloumn[/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="class: cms_table, width: 182"]
<tbody>[TR]
[TD]if I type from 1 to 15 in D column automatically the rate shd come in E colum[/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]please help me and do the need full[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Re: multiplying 3 diifrent rate for diffrent nos of products

I regret that this is not very helpful.
I think that you are discussing discounted pricing on volume of product.
Please explain exactly what you require rather have someone try to second guess your intention.
 
Upvote 0
Re: multiplying 3 diifrent rate for diffrent nos of products

hi
you are correct it is discounted pricing on volume of product
when customer enter his desired quantity . pricing for the same should appear
 
Upvote 0
Re: discounted pricing on volume of product

hi
table woud be[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]minimum order qty 5[/TD]
[TD]minimum order qty 10[/TD]
[TD]minimum order qty 15[/TD]
[TD]Quantity required[/TD]
[TD]price[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]14[/TD]
[TD]12[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

the above table for reference which tell discounted pricing on volume of product
i want formula for this. when customer enter desired quantity automatically in price column rate should appear.

Please help

harish
 
Upvote 0
Re: discounted pricing on volume of product

This small table does what your table requires:
[TABLE="width: 384"]
<colgroup><col style="width:48pt" width="64" span="6"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Prod[/TD]
[TD="class: xl63, width: 64"]Rate 5[/TD]
[TD="class: xl63, width: 64"]Rate 10[/TD]
[TD="class: xl63, width: 64"]Rate 15[/TD]
[TD="class: xl63, width: 64"]Qty[/TD]
[TD="class: xl63, width: 64"]Price[/TD]
[/TR]
[TR]
[TD="class: xl63"]Apple[/TD]
[TD="class: xl63, align: right"]14[/TD]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]20[/TD]
[TD="class: xl63, align: right"]200[/TD]
[/TR]
</tbody>[/TABLE]

This goes from Col A to Col F.
The formula in F2 is:
=IF(E2<5,"N/A",IF(AND(E2>4,E2<10),E2*B2,IF(AND(E2>9,E2<15),E2*C2,E2*10)))

Not: I have included the situation where a value below your lowest value is reported as Not Available, it is not a computer error.
 
Upvote 0
Re: discounted pricing on volume of product

This small table does what your table requires:
[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Prod[/TD]
[TD="class: xl63, width: 64"]Rate 5[/TD]
[TD="class: xl63, width: 64"]Rate 10[/TD]
[TD="class: xl63, width: 64"]Rate 15[/TD]
[TD="class: xl63, width: 64"]Qty[/TD]
[TD="class: xl63, width: 64"]Price[/TD]
[/TR]
[TR]
[TD="class: xl63"]Apple[/TD]
[TD="class: xl63, align: right"]14[/TD]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]20[/TD]
[TD="class: xl63, align: right"]200[/TD]
[/TR]
</tbody>[/TABLE]

This goes from Col A to Col F.
The formula in F2 is:
=IF(E2<5,"N/A",IF(AND(E2>4,E2<10),E2*B2,IF(AND(E2>9,E2<15),E2*C2,E2*10)))

Not: I have included the situation where a value below your lowest value is reported as Not Available, it is not a computer error.

dear sir,

in this formula b clooumn and c coloumn is not reading if i type 4 it shd mulitiply with 14 and if type 7 it shd multiply with 12... hope uu understood my query
please help
 
Upvote 0
Re: discounted pricing on volume of product

Apologies, I know that in your first comment you were mentioning "typing 1 to 15" but after this table of yours
table woud be[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]minimum order qty 5[/TD]
[TD]minimum order qty 10[/TD]
[TD]minimum order qty 15[/TD]
[TD]Quantity required[/TD]
[TD]price[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]14[/TD]
[TD]12[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]
I was thinking that the customer's minimum order was 5 and so I structured the formula around that value.
Ok then, going back to allowing a minimum order of 1 then enter the formula:

=IF(E2<6,E2*B2,IF(AND(E2>4,E2<11),E2*C2,E2*D2))

into F2.
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,193
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top