Read Cell Value and distribute value equally

[kashyap_dave]

2%	Y
25%	Y
15%	Y
10%	N
5%	Y
5%	Y

Consider two columns in the table,Col A and Col B
I need to write a formula where I read the cell value of Col B first.

IF ColB cell value is 'Y', I skip the cell and check the next cell in Col B. If cell Value is 'N', I need to pick the corresponding cell value in Col A, distribute the value across the whole column equally.

For instance in the above example, B4 is N ,I pick 10% and distribute 10% in the rest of cells of Column A. i.e. add 1.66 in each of the rows in Col A and update that value in Col A as 0

[bebo021999]

Where does "1.66" come from? Could you show us the "AFTER" outcome for each row?

[AliGW]

You might want to go back and thank those who helped you here before.

By the way, nothing is treated as urgent here: it's a free help forum, not a paid service.

[kashyap_dave]

Hi Sorry for the confusion.
I miscalculated.

So basically in the above example, I pick those cell value in Col A where corresponding cell value in Col B is 'N', in this case it's B4, and hence I pick A4 which is 10%

The goal is to divide 10% equally in the rest of the remaining rows of Col A. i.e (value of cell/rows in col A) (10/5=2%) So I'll add 2% to each row in Col A and make A4 as 0

After table:

4% Y
27% Y
17% Y
0% N
7% Y
7% Y


Please reach out for further clarity. Much appreciated

[kashyap_dave]

Where does "1.66" come from? Could you show us the "AFTER" outcome for each row?

Hi Sorry for the confusion.
I miscalculated.

So basically in the above example, I pick those cell value in Col A where corresponding cell value in Col B is 'N', in this case it's B4, and hence I pick A4 which is 10%

The goal is to divide 10% equally in the rest of the remaining rows of Col A. i.e (value of cell/rows in col A) (10/5=2%) So I'll add 2% to each row in Col A and make A4 as 0

After table:

4% Y
27% Y
17% Y
0% N
7% Y
7% Y


Please reach out for further clarity. Much appreciated

[bebo021999]

Data % is in A1:A6, "Yes/No" is in B1:B6,

Next column, C1 use:

=(B1="Y")*(A1+SUMIF($B$1:$B$6,"N",$A$1:$A$6)/COUNTIF($B$1:$B$6,"Y"))




Drag down

[kashyap_dave]

Yes this absolutely works fine.
You're a star. Made my day

thanks again