multiplying 3 different rate for different nos of products

H

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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
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

BrianJN1 said:
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.
Click to expand...

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]
Click to expand...
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
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question Question
Can someone help me understand Bayesian averages?
Replies
1
Views
175
FormulasOnly
F
S
  • Question Question
Shifting cell reference
Replies
5
Views
113
Cubist
Cubist
CubaRJ
  • Question Question
Calculate Overtime with 2 codes for the same date
Replies
0
Views
191
CubaRJ
CubaRJ
S
  • Solved
How to sum a column - minus one row , to avoid circular calculations ?
Replies
6
Views
337
StrawberryDreams
S
L
  • Solved
Updating data in cell, run the macro again and it should not happen.
2 3 4
Replies
34
Views
1K
mumps
mumps

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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