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

[iqubal82]

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,

 

[Fluff]

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]

Please refer below my excel range and help.

Thanks,

SALES ESTIMATE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	STAR JEWELLERY
2	10, GOLD SOUQ COMPLEX, NAGAPATTINAM
3	TEL: 04365 247830
4	CUSTOMER DETAILS:								Date :	19/09/2020
5	Mr/Mrs. John								Est No:	EST01
6									Gold Per Grm :	₹ 4,920.00	Gold rate will change day by day as per jelwes association
7									Silver Per Grm :	₹ 70.00	Silver rate will change day by day as per jewels association
8	ESTIMATE
9	S.No.	PRODUCT	PCS	PURITY	WEIGHT	METAL WASTAGE	WASTAGE	RATE	ADDITION	AMOUNT
10	1	Chain	1	916	2.000	0.200		₹ 4,920.00	₹ 500.00	₹ 11,324.00	This is METAL format value
11	2	Ring	1	916	2.000		10.000	₹ 4,920.00	₹ 500.00	₹ 11,324.00	This is WASTAGE format valve but both are same value result
12	3	Nanal	1	NK	2.000			₹ 4,920.00	₹ 500.00	₹ 10,340.00	This is NK purity value
13	4	Thali	1	NK	2.000			₹ 4,920.00	₹ 500.00	₹ 10,340.00	This is NK purity value but bott or same value result
14	5	ACPL Chain	1	92.5	10.000			₹ 250.00	₹ 500.00	₹ 3,000.00	This is ACPL product value rate fixing ourselves
15	6	Silver Chain	1	72	10.000		10.000	₹ 70.00	₹ 500.00	₹ 1,270.00	This is WASTAGE format valve
16									Total	₹ 47,598.00
EST01

Cell Formulas
Range	Formula
J4	J4	=TODAY()
F10	F10	=E10*10%
D10	D10	=IF(B10="","",IF(OR(B10={"Nanal","Thali"}),"NK",IF(ISNUMBER(FIND("Silver",B10)),72,IF(ISNUMBER(FIND("ACPL",B10)),92.5,916))))
D11:D15	D11	=IF(OR(B11={"Nanal","Thali"}),"NK",IF(ISNUMBER(FIND("Silver",B11)),72,IF(ISNUMBER(FIND("ACPL",B11)),92.5,916)))
H10:H15	H10	=IF(OR(COUNTIF(D10,{916,"NK"})),$J$6,IF(D10=72,$J$7,IF(D10=92.5,250,"")))
J10	J10	=IF(H10="","",SUM(E10+F10)*H10+I10)
J11,J15	J11	=SUM(H11*E11)+(E11*G11/100*H11)+I11
J12:J14	J12	=(H12*E12)+I12
J16	J16	=SUM(J10:J15)