The "J11" cell auto select the wastage calculation fixing by anyone of wastage range

iqubal82

Banned user
Joined
Jul 28, 2020
Messages
8
Dear Experts,

The metal would be considered the estimate calculation is 2.000+0.200*4920+500 = 11324

The another wastage would be considered the estimate calculation is 2.200*10%*4920+500 = 11324

The ACPL product would be considered the estimate calculation is 10.000*250+500 = 3000

The Silver product would be considered the estimate calculation is 10.000*10%*70+500 = 1270

I want suitable formula in J11, so multi calculation result in one cell. Please refer and help.


Thanks,
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
Cross posted Auto update funtions in excel

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.
 

iqubal82

Banned user
Joined
Jul 28, 2020
Messages
8
Please refer below my excel range and help.

Thanks,


SALES ESTIMATE.xlsx
ABCDEFGHIJKLMNOP
1STAR JEWELLERY
210, GOLD SOUQ COMPLEX, NAGAPATTINAM
3TEL: 04365 247830
4CUSTOMER DETAILS:Date :19/09/2020
5Mr/Mrs. JohnEst No:EST01
6Gold Per Grm :₹ 4,920.00Gold rate will change day by day as per jelwes association
7Silver Per Grm :₹ 70.00Silver rate will change day by day as per jewels association
8ESTIMATE
9S.No.PRODUCTPCSPURITYWEIGHTMETAL WASTAGEWASTAGERATEADDITIONAMOUNT
101Chain19162.0000.200₹ 4,920.00₹ 500.00₹ 11,324.00This is METAL format value
112Ring19162.00010.000₹ 4,920.00₹ 500.00₹ 11,324.00This is WASTAGE format valve but both are same value result
123Nanal1NK2.000₹ 4,920.00₹ 500.00₹ 10,340.00This is NK purity value
134Thali1NK2.000₹ 4,920.00₹ 500.00₹ 10,340.00This is NK purity value but bott or same value result
145ACPL Chain192.510.000₹ 250.00₹ 500.00₹ 3,000.00This is ACPL product value rate fixing ourselves
156Silver Chain17210.00010.000₹ 70.00₹ 500.00₹ 1,270.00This is WASTAGE format valve
16Total₹ 47,598.00
EST01
Cell Formulas
RangeFormula
J4J4=TODAY()
F10F10=E10*10%
D10D10=IF(B10="","",IF(OR(B10={"Nanal","Thali"}),"NK",IF(ISNUMBER(FIND("Silver",B10)),72,IF(ISNUMBER(FIND("ACPL",B10)),92.5,916))))
D11:D15D11=IF(OR(B11={"Nanal","Thali"}),"NK",IF(ISNUMBER(FIND("Silver",B11)),72,IF(ISNUMBER(FIND("ACPL",B11)),92.5,916)))
H10:H15H10=IF(OR(COUNTIF(D10,{916,"NK"})),$J$6,IF(D10=72,$J$7,IF(D10=92.5,250,"")))
J10J10=IF(H10="","",SUM(E10+F10)*H10+I10)
J11,J15J11=SUM(H11*E11)+(E11*G11/100*H11)+I11
J12:J14J12=(H12*E12)+I12
J16J16=SUM(J10:J15)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

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
Top