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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
110
FormulasOnly
F
CubaRJ
  • Question Question
Calculate Overtime with 2 codes for the same date
Replies
0
Views
179
CubaRJ
CubaRJ
S
  • Solved
How to sum a column - minus one row , to avoid circular calculations ?
Replies
6
Views
326
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
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
183
Pbobas
P

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

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