ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Formula help (https://www.excelbanter.com/excel-discussion-misc-queries/216306-formula-help.html)

Hardeep_kanwar[_2_]
Formula help
 
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
Formula help
 
In c2 (headers in Row 1???):
=IF(COUNTIF($A$2:A2,A2)1,"",B2/COUNTIF(A:A,A2))


Hardeep_kanwar wrote:

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

Hardeep_kanwar[_2_]
Formula help
 
Thanks for Quick Reply

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

Hardeep kanwar

"Dave Peterson" wrote:

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


Hardeep_kanwar wrote:

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

Dave Peterson
Formula help
 
What's in B2?

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



Hardeep_kanwar wrote:

Thanks for Quick Reply

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

Hardeep kanwar

"Dave Peterson" wrote:

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


Hardeep_kanwar wrote:

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


--

Dave Peterson

Hardeep_kanwar[_2_]
Formula help
 


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" wrote:

What's in B2?

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



Hardeep_kanwar wrote:

Thanks for Quick Reply

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

Hardeep kanwar

"Dave Peterson" wrote:

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


Hardeep_kanwar wrote:

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


--

Dave Peterson

Hardeep_kanwar[_2_]
Formula help
 
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

"Hardeep_kanwar" wrote:



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" wrote:

What's in B2?

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



Hardeep_kanwar wrote:

Thanks for Quick Reply

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

Hardeep kanwar

"Dave Peterson" wrote:

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


Hardeep_kanwar wrote:

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


--

Dave Peterson

Dave Peterson
Formula help
 
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))





Hardeep_kanwar wrote:

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" wrote:

What's in B2?

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



Hardeep_kanwar wrote:

Thanks for Quick Reply

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

Hardeep kanwar

"Dave Peterson" wrote:

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


Hardeep_kanwar wrote:

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


--

Dave Peterson


--

Dave Peterson

Hardeep_kanwar[_2_]
Formula help
 

That's Better

Works Perfectly.

And Sorry For Inconvenience from my side

Thanks Sir

Most Appreciate


Hardeep kanwar


"Dave Peterson" wrote:

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))





Hardeep_kanwar wrote:

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" wrote:

What's in B2?

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



Hardeep_kanwar wrote:

Thanks for Quick Reply

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

Hardeep kanwar

"Dave Peterson" wrote:

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


Hardeep_kanwar wrote:

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


--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 04:20 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com