QUERY questions

J

Jewells0905

New Member
Joined
Mar 10, 2024
Messages
39
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I am trying to write a function to display country names for countries in Asia and the total number of people who can read for each of those countries, not including any country that has an empty cell in column F. Order the results by number of readers from high to low, and label the column with the number of readers "# of Readers" and ensure that the value is displayed as a whole number. I tried using this formula but it is only producing the headers. =QUERY(countries, "SELECT A, C * F / 100 WHERE B contains 'Asia' AND F is not null ORDER BY C * F / 100 DESC LABEL C * F / 100 '# of Readers'", 1)

This is a named range countries. And this is being done in Google Sheets

CountryRegionPopulationArea (sq. mi.)GDP ($ per capita)Literacy (%)
AfghanistanASIA (EX. NEAR EAST)3105699764750070036.00
AlbaniaEASTERN EUROPE358165528748450086.50
AlgeriaNORTHERN AFRICA329300912381740600070.00
American SamoaOCEANIA57794199800097.00
AndorraWESTERN EUROPE7120146819000100.00
AngolaSUB-SAHARAN AFRICA121270711246700190042.00
AnguillaLATIN AMER. & CARIB13477102860095.00
Antigua & BarbudaLATIN AMER. & CARIB691084431100089.00
ArgentinaLATIN AMER. & CARIB3992183327668901120097.10
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
I have a formula that works, however, I am not able to get the numbers to round. The formula I am using is =QUERY(countries, "SELECT A, C * F / 100 WHERE B contains 'ASIA' AND F is not null ORDER BY C * F / 100 DESC LABEL C * F / 100 '# of Readers'", 1) however, I can not get the round to whole number to work. When I try this formula it gives an error =QUERY(countries, "SELECT A, ROUND(C * F / 100) WHERE B contains 'ASIA' AND F is not null ORDER BY C * F / 100 DESC LABEL ROUND(C * F / 100) '# of Readers'", 1)
 
Upvote 0
=QUERY(countries, "SELECT A, C * F / 100 WHERE B contains 'ASIA' AND F is not null ORDER BY C * F / 100 DESC LABEL C * F / 100 '# of Readers'FORMAT C*F/100 '#'", 1) - correct formula
 
Upvote 0
Solution
You must log in or register to reply here.

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top