Show the first digit of a citys population,when city starts from Δ.Δ or Κ.Δ

[Dinos.fa]

Hello,just made a account to the forum.
I 'm studying Economics,and i have a excersise to do on Excel,where i have to use the Benford law in an example of a country's population.

there is huge list with information about the population of the country in each city adn "sub city" of it . Copy pasting a very small part from the excersise:

ΔΗΜΟΣ ΔΡΑΜΑΣ 55.632
Δ.Δ.Δράμας 43.485
Δ.Δ.Καλλιφύτου 1.149
Δ.Δ.Καλού Αγρού 1.216
Δ.Δ.Κουδουνίων 885
Κ.Δ.Λιβαδερού 146
Δ.Δ.Μακρυπλαγίου 84
Δ.Δ.Μαυροβάτου 714
Δ.Δ.Μικροχωρίου 598
ΔΗΜΟΣ ΔΟΞΑΤΟΥ 11.000


On the left it's the name of the sub citys and on the right the total population of each one of them.

On previous part of the excersise i had to find with a comand how many the sub citys exist in the list.So i use the the command > =COUNTIFS(C1:C7244; "Δ.Δ*"; D1:D7244; ">100000" )
which gives me the result that i want.But as u can see the names of the sub citys start with Δ.Δ or Κ.Δ.So in order to finnd out the total nuber of sub citys i had to make a seperate command to count the Κ.Δ aswell > =COUNTIFS(C1:C7244; "K.*"; D1:D7244; ">100000" ) which works fine,as intended.

The first question: is there a way to make the 2 commands in one?? tried the =COUNTIFS(C1:C7244; OR("Δ.Δ*";"Κ.*"); D1:D7244; ">100000" ) But dsn't work as intended ...



And the 2nd question:
In Benford's law i need the 1st Digit ofthe population of each sub city.Becouse in the list of sub citys there are major citys aswell i can't just use the =Left(D1) and drag it to the end of the list.

I need a command that will will give me the First digit of the of population for each sub city it excists in the list.Like, When u see Δ.Δ or Κ.Δ on the city's name then go to the citys population,and show my the 1st digit of that population !

Hope u undrestand what i mean :D
ty

[oeldere]

for your second question

=if(Or(a2=Δ.Δ,a2=Κ.Δ),right(left(a2,4),1),"")

and for you first question:

make an helpcolumn in column C:

c2=left(a2,1)

d2=if(b2>10.000,"yes","no")

After that you can make an pivot table of that data.

[Dinos.fa]

It doesen't work for me ... :/

[oeldere]

Please add an excel example, without confidentional information.

Please also add the desired (expected) result.

Then we can see,what goes wrong.

[Dinos.fa]

if u can explain me how to "upload the list of my excersise i will do:D how can i do it like u did in your post the "excel look alike"?

[oeldere]

The format should be your Original file, without confidentional information.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Dinos.fa]

ok,hope now it's right

[Dinos.fa]

On C13 says Δ.Δ.Δράμας the expected result is the first digit from D13, in this case noumber 4.

I wasnt the 1st digit's of citys that have the Δ.Δ or Κ.Δ letters in front of them.

[oeldere]

See in the green cells if this is what you want.

Please reply.

[Dinos.fa]

Srry for taking a long time to answer you.

Unfortunatly,no. the Noubers that i need are the first digits from the numbers on Columns with the letter E.

For example the :Δ.Δ.Δράμας has a population of 43.485 people.i need the nouber 4
and the :Δ.Δ.Καλλιφύτου has a polulation of 1.149 people si this time i need the number 1.
Hope u undrestand which number i need now

[oeldere]

It's just a small change of the formula.

Please change the topic title, then I'm allowed to add the file.

[shg]

In D8, =IF(OR(LEFT(C8,3)="Δ.Δ", LEFT(C8,3)="Κ.Δ"), LEFT(E8), "")

[Dinos.fa]

:D:D:D Many thanks it works Both of you, Oeldere and shg.

My second question is now answered! anything you can do about my first??!

[oeldere]

See the pivot table on the seperate worksheet.

there is only 1 city thats > 10.000

I showed also the No values, but that is not neccesary.

[Dinos.fa]

TY guys i will make a new post,for the upcoming (if there are any) questions ty

[oeldere]

Did you take a look at the pivot table, and did it work for you?

You can add an rep. to the one's who helped you by clicking on the star on the left side.

[Dinos.fa]

Jup,they are fine!

I added rep in both of you.