Formula help

[Hardeep_kanwar]

Hi! Everybody

I have a data Like This
Column A ColumnB
Abc 50
Abc 50
Def 80
Def 80
Def 80
xyz 42
xyz 42
xyz 42
xyz 42
Pqr 10

Now i want to Divide each data like ABC=50 in 2 row i want to Divide 50/2
Def=80 in 3Rows i want to Divide 80/3
Xyz=42 in 4Rows i want to Divide 42/4
And Pqr=10 want to Divide 10/1

Like This:
Column A ColumnB column C
Abc 50 25
Abc 50
Def 80 26.66666667
Def 80
Def 80
xyz 42 10.5
xyz 42
xyz 42
xyz 42
Pqr 10 10

Sometimes Some data goes to 30 or even40.Actually it depend on the numbers
of data i have if i have 20 rows with same data then divide by 20
If18 then divide by 18 and so on

I don't want to Delete any data

Any help will be Appreciated

Thanks in Advance

Hardeep kanwar

 

[Dave Peterson]

In c2 (headers in Row 1???):
=IF(COUNTIF($A$2:A2,A2)>1,"",B2/COUNTIF(A:A,A2))

 

[Hardeep_kanwar]

Thanks for Quick Reply

but Sir When i put this Formula in C@ it Show only #VALUE!

Hardeep kanwar

 

[Dave Peterson]

What's in B2?

You'll have to adjust the row numbers to match the starting row of your data.

 

[Hardeep_kanwar]

S No. Name Numbers Total
1 Abc 50 IF(COUNTIF($B$2:B2,B2)>1,"",C2/COUNTIF(B:B,B2))
2 Abc 50
3 Def 80 #VALUE!
4 Def 80
5 Def 80
6 Pqr 10 #VALUE!
7 xyz 42 #VALUE!
8 xyz 42
9 xyz 42
10 xyz 42

Please Tell where i am Wrong

Hardeep kanwar

 

[Hardeep_kanwar]

Sir If you don't Mind

Could you please try this Formula in your Sheet.

May be i was wrong But i am 200% sure i am not wrong

Hardeep kanwar

S No. Name Numbers Total
1 Abc 50 IF(COUNTIF($B$2:B2,B2)>1,"",C2/COUNTIF(B:B,B2))
2 Abc 50
3 Def 80 #VALUE!
4 Def 80
5 Def 80
6 Pqr 10 #VALUE!
7 xyz 42 #VALUE!
8 xyz 42
9 xyz 42
10 xyz 42

Please Tell where i am Wrong

Hardeep kanwar

 

[Dave Peterson]

You didn't change the row number to 1 -- if that's where your data started. And
you changed B2 to C2. I thought your values were in column B.

But I'm confused about where your data is.

If your names are in column A (starting in A1) and your numbers are in column B
(starting in B1), then try:

=IF(COUNTIF($A$1:A1,A1)>1,"",B1/COUNTIF(A:A,A1))

S No. Name Numbers Total
1 Abc 50 IF(COUNTIF($B$2:B2,B2)>1,"",C2/COUNTIF(B:B,B2))
2 Abc 50
3 Def 80 #VALUE!
4 Def 80
5 Def 80
6 Pqr 10 #VALUE!
7 xyz 42 #VALUE!
8 xyz 42
9 xyz 42
10 xyz 42

Please Tell where i am Wrong

Hardeep kanwar

 

[Hardeep_kanwar]

That's Better

Works Perfectly.

And Sorry For Inconvenience from my side

Thanks Sir

Most Appreciate


Hardeep kanwar