Formula that look upwards cell if reference cell is blank ( Empty ) till the value is found and calculate percentage based on that value.

[Amit Desai]

Material Code

MATERIAL DISCRIPSION

UOM

%

Part Weght

OPENING STOCK

Material Issue

OK Count

REJ.

Total SFG Qty

TOTAL CONSUMPTION

Formula Sample

R/M%

actual Result should be

4214211725​

ac

g

87.50%​

1593​

220​

200​

254

0

254​

404.622​

=K2*D2

354.04​

=K2*D2

1199000002​

ab

g

12.50%​

1593​

0​

0​

0

=K2*D3

50.58​

=K2*D3 ( as the K3 cell is empty it needs to lookup ward till the value found and multiply % based on found value.

4214210231​

ad

g

88.50%​

960​

355​

0​

187

6

193​

185.28​

=K4*D4

163.96​

4214210380​

aa

g

1.50%​

960​

0​

0​

0

=K4*D5

2.78​

1199000052​

ar

g

10.00%​

960​

0​

0​

0

=K4*D6

18.53​

1312000156​

ap

Nos

0.01%​

960​

0​

0​

0

=K4*D7

0.01​

4214210239​

ao

g

85.00%​

216​

70​

250​

1042

6

1048​

226.368​

=K8*D8

192.41​

1199000321​

am

g

15.00%​

216​

0​

0​

0

=K8*D9

33.96​

4214210249​

an

g

36.20%​

63​

0​

0​

1456

12

1468​

92.484​

=K10*D10

33.48​

4214210260​

ab

g

55.80%​

63​

0​

0​

0

=K8*D11

126.31​

4214210261​

af

g

2.00%​

63​

0​

0​

0

=K8*D12

4.53​

4214211516​

aj

g

6.00%​

63​

0​

0​

0

=K8*D13

13.58​

hope above table help you to understand the requirement .

 

[Fluff]

How about

=LOOKUP(2,1/(K$2:K2<>""),K$2:K2)*D2

 

[Peter_SSs]

Because your col K values are numeric, you should also be able to use this.

=LOOKUP(9^9,K$2:K2)*D2

 

[Amit Desai]

Namaste From India ,

@Fluff you are magician... your formula worked perfectly as needed. Thanks a Lot.....
@Peter_SSs , Thanks for your attempt, but in your formula give different result then what i was expectation. Thanks a lot.

Lots of love to both of you for your valuable time and helping nature.

Take Care.

 

[Peter_SSs]

@Peter_SSs , Thanks for your attempt, but in your formula give different result then what i was expectation.

Hmm, I wonder what then is different between the sample data that you posted and what you are actually using the formulas with?
Here are the two formulas with the sample data you posted. Identical results.

24 03 19.xlsm
	D							K		M	N
1	%							TOTAL CONSUMPTION		Post #2	Post #3
2	87.50%							404.622		354.04425	354.04425
3	12.50%									50.57775	50.57775
4	88.50%							185.28		163.9728	163.9728
5	1.50%									2.7792	2.7792
6	10.00%									18.528	18.528
7	0.01%									0.018528	0.018528
8	85.00%							226.368		192.4128	192.4128
9	15.00%									33.9552	33.9552
10	36.20%							92.484		33.479208	33.479208
11	55.80%									51.606072	51.606072
12	2.00%									1.84968	1.84968
13	6.00%									5.54904	5.54904
Amit Desai

Cell Formulas
Range	Formula
M2:M13	M2	=LOOKUP(2,1/(K$2:K2<>""),K$2:K2)*D2
N2:N13	N2	=LOOKUP(9^9,K$2:K2)*D2

 

[Amit Desai]

Hi @Peter_SSs

Apology for very late reply. I don't know what was the issue but when i have used your formula it was calculating result based on 1st cell value which is 404.662 for all trailing cell.

Thanks again for your valuable time. Take Care......

Regards,
Amit Desai.

 

[Peter_SSs]

I don't know what was the issue but when i have used your formula it was calculating result based on 1st cell value which is 404.662 for all trailing cell.

It doesn't really matter as you have a working solution but perhaps you accidentally included an extra $ sign in my formula as that would cause those results.

=LOOKUP(9^9,K$2:K$2)*D2

24 03 19.xlsm
	D							K		M	N	O
1	%							TOTAL CONSUMPTION		Post #2	Post #3	Extra $ sign
2	87.50%							404.622		354.04425	354.04425	354.04425
3	12.50%									50.57775	50.57775	50.57775
4	88.50%							185.28		163.9728	163.9728	358.09047
5	1.50%									2.7792	2.7792	6.06933
6	10.00%									18.528	18.528	40.4622
7	0.01%									0.018528	0.018528	0.0404622
8	85.00%							226.368		192.4128	192.4128	343.9287
9	15.00%									33.9552	33.9552	60.6933
10	36.20%							92.484		33.479208	33.479208	146.473164
11	55.80%									51.606072	51.606072	225.779076
12	2.00%									1.84968	1.84968	8.09244
13	6.00%									5.54904	5.54904	24.27732
Amit Desai

Cell Formulas
Range	Formula
M2:M13	M2	=LOOKUP(2,1/(K$2:K2<>""),K$2:K2)*D2
N2:N13	N2	=LOOKUP(9^9,K$2:K2)*D2
O2:O13	O2	=LOOKUP(9^9,K$2:K$2)*D2

 

[Amit Desai]

It doesn't really matter as you have a working solution but perhaps you accidentally included an extra $ sign in my formula as that would cause those results.

=LOOKUP(9^9,K$2:K$2)*D2

Hi @Peter_SSs ,

You are right i have made mistake during edit the column address. i have reapply the formula and it worked same as you have mentioned.

My sincere apology and thanks a lot for your kind help and time.

Is there any book or course which taught such complicated formulas? or it required knowledge of coding to get in-depth knowledge of excel formulas? As an end user we hardly apply 20 to 30 ( or less then that ) types of formulas .

Regards,
Amit Desai.

 

[Peter_SSs]

Glad we were at least able to work out what had happened.

Is there any book or course which taught such complicated formulas? or it required knowledge of coding to get in-depth knowledge of excel formulas? As an end user we hardly apply 20 to 30 ( or less then that ) types of formulas .

A large proportion of what I know I learned here in the forum & a lot of trial-and-error, not books or courses.

 

[Peter_SSs]

Please mark the post as the solution that best actually answered your question. If no post answered your question then it is fine to not mark any post. I have removed the solution mark from post 9 as it certainly is not a solution to the thread question.