Hi users of Numista
I’m going to buy flips for my coins but the issue is that I need to see how many flips I need for each diameter. For example the 15mm flip I would need to sort coins that are from 0-15mm and for 17.50mm I would sort them 15.01-17.50mm (then noting this down in a Google sheets document) but I’m unable to do this in a sufficient manner because the only way I’ve found is typing in the diameter box then going into the page of each coin that fits the criteria and counting one by one which takes ages.
So can anyone help me to sort them much more efficiently or if not I’ll put a request in for a feature that will help solve this issue.
Sorting diameter of coins/Exonumia for buying holders. [solved]
39 posts • viewed 351 times
» Quick access to the last post
Worldwide collection
Joined: 1 Mar 2020
Posts: 2253
Posts: 2253
Posted: 29 Nov 2021, 13:24
#
Edited: 29 Nov 2021, 13:26
Edited: 29 Nov 2021, 13:26
Posted:
#
Quote: "smvdbrink"Export to Excel, sort by Diameter and create an IF-statement to determine which coinholder each coin needs. Then do a count per holder size.I don’t have Excel but I have exported it to Numbers (Mac version of Excel).
Then I sorted it by diameter.
So is there anything else I need to do to it? Or it’s good enough that I can put this information in to Google sheets.
Hi to whoever is reading this. Did you know that TYPEWRITER (on a QWERTY keyboard) is the longest word you can type using only the letters on one row of the keyboard.
Posted:
#
Edited:
Edited:
Y'all confusin the poor boy. 
smvdbrink formulas work very well except add = before them. Also no need to first sort by diameter; just sum each column. leopiccionia suggestion is probably worth doing also and can be easily done by first adding another column then using that column instead of the diameter.
Edit: Note that if you have more than one of the same type/year you'll need to first multiply by the QTY column before summing if you want to put all in flips.
smvdbrink formulas work very well except add = before them. Also no need to first sort by diameter; just sum each column. leopiccionia suggestion is probably worth doing also and can be easily done by first adding another column then using that column instead of the diameter.
Edit: Note that if you have more than one of the same type/year you'll need to first multiply by the QTY column before summing if you want to put all in flips.
Worldwide collection
Joined: 1 Mar 2020
Posts: 2253
Posts: 2253
Posted: 29 Nov 2021, 18:06
#
Edited: 29 Nov 2021, 18:29
Edited: 29 Nov 2021, 18:29
Posted:
#
Edited:
Edited:
Quote: "smvdbrink"Why would you put it in Google Sheets if you already have Numbers?I've downloaded the list again with more of the selected information in Numbers because it doesn't work on Sheets for any of the three formats.
What you need to do is the following. Go to the end of your sheet and take a field for each size coinholder. For each of the sizes, you create a COUNTIFS formula, except for the first one, that should be a COUNTIF. It should look something like this:
For the 15mm coin holder: COUNTIF(<column>,"<=15")
For the 17.5 mm coin holder: COUNTIFS(<column>,">15",<column>,"<=17.5")
For the 20 mm coin holder: COUNTIFS(<column>,">17.5",<column>,"<=20")
For the 22.5 mm coin holder: COUNTIFS(<column>,">20",<column>,"<=22.5")
and so on and so on. Everywhere it says <column>, replace it with the actual column where the diameters are.
Anyway if I put the formula into AA would I have to type COUNTIF(<column>,"<=15") column whatever letter to get my answer. But there is several other issues on it includes my coins that are in the "Duplicates" of my collection which won't go into flips because one day they are going to be up for sale on Ebay. Plus how I've catalogued my coins is not really the most organised so that might affect how many flips I need.
EDIT: Removed the "Duplicates" from the list.
Hi to whoever is reading this. Did you know that TYPEWRITER (on a QWERTY keyboard) is the longest word you can type using only the letters on one row of the keyboard.
Posted:
#
Quote: "rsirian1"Y'all confusin the poor boy.Yes most of this is left me head scratching and many trials and errors.
smvdbrink formulas work very well except add = before them. Also no need to first sort by diameter; just sum each column. leopiccionia suggestion is probably worth doing also and can be easily done by first adding another column then using that column instead of the diameter.
Edit: Note that if you have more than one of the same type/year you'll need to first multiply by the QTY column before summing if you want to put all in flips.
Hi to whoever is reading this. Did you know that TYPEWRITER (on a QWERTY keyboard) is the longest word you can type using only the letters on one row of the keyboard.
Posted:
#
I did a simplified list. Only downloaded diameters and some other identifying data. Notice the formulas at the top for each column. Carry them down the column then sum at the bottom. Note this will only count one coin of each type/date/mint/etc. If you're going to flip more than one of the same coin you'll need to download the quantity also and use that to multiply the results before summing.
Posted:
#
Quote: "rsirian1"I did a simplified list. Only downloaded diameters and some other identifying data. Notice the formulas at the top for each column. Carry them down the column then sum at the bottom. Note this will only count one coin of each type/date/mint/etc. If you're going to flip more than one of the same coin you'll need to download the quantity also and use that to multiply the results before summing.Keep getting Syntax errors.
Hi to whoever is reading this. Did you know that TYPEWRITER (on a QWERTY keyboard) is the longest word you can type using only the letters on one row of the keyboard.
Posted:
#
Quote: "Worldwide collection"Keep getting Syntax errors.You may be typing it wrong. I just tried using Google Sheets and the syntax was find.
post a picture of the cell contents.
Posted:
#
Edited:
Edited:
Quote: "Worldwide collection"Have you tried this?
Quote: "rsirian1"I did a simplified list. Only downloaded diameters and some other identifying data. Notice the formulas at the top for each column. Carry them down the column then sum at the bottom. Note this will only count one coin of each type/date/mint/etc. If you're going to flip more than one of the same coin you'll need to download the quantity also and use that to multiply the results before summing.Keep getting Syntax errors.
https://www.google.com/search?q=how+to+use+formulas+in+apple+numbers
They say "Pecunia non olet", but I know better...
Posted:
#
Quote: "rsirian1"I've managed to Export from Numbers to Google sheets so were on the same page.
Quote: "Worldwide collection"Keep getting Syntax errors.You may be typing it wrong. I just tried using Google Sheets and the syntax was find.
post a picture of the cell contents.
It either shows an error,NA or 0.
Hi to whoever is reading this. Did you know that TYPEWRITER (on a QWERTY keyboard) is the longest word you can type using only the letters on one row of the keyboard.
Posted:
#
Edited:
Edited:
"Wrong number of arguments." Delete the ,N2521. Also, change N1 to N2 (it's in the 2nd row).
Then select the cell, grab the square in the bottom right and drag down to the last row.
BTW...I didn't see where you switched to Numbers. I thought you were still using Google Sheets.
Then select the cell, grab the square in the bottom right and drag down to the last row.
BTW...I didn't see where you switched to Numbers. I thought you were still using Google Sheets.
Posted:
#
Quote: "rsirian1""Wrong number of arguments." Delete the ,N2521. Also, change N1 to N2 (it's in the 2nd row).Thanks now it works.
Then select the cell, grab the square in the bottom right and drag down to the last row.
BTW...I didn't see where you switched to Numbers. I thought you were still using Google Sheets.
No the list of my collection was in Numbers and the holder sizes in Sheets so I exported the list from Numbers to Google sheets from a different method.
But the only thing that might cause trouble is that the command =COUNTIF(N2,"<=15") is for the N column withe the diameters but the quantity in each diameter in the T column (See photo below) does that count that in or not?
Its getting late here so I will carry this on in the morning (UK time)
Hi to whoever is reading this. Did you know that TYPEWRITER (on a QWERTY keyboard) is the longest word you can type using only the letters on one row of the keyboard.
Posted:
#
No it won't count the numbers in column T. What you need to do is on a new column use the formula =T3*AB3 assuming the count is in column AB like in your earlier picture. Select the cell and drag it down like before.
Like this:
Like this:
Posted:
#
Quote: "rsirian1"No it won't count the numbers in column T. What you need to do is on a new column use the formula =T3*AB3 assuming the count is in column AB like in your earlier picture. Select the cell and drag it down like before.Your method works, because I don't have many coins that are 15mm and smaller I counted them all manually and yes it is correct. But I do have an error (Pic below) about the how the command sorts the values.
Like this:
Hi to whoever is reading this. Did you know that TYPEWRITER (on a QWERTY keyboard) is the longest word you can type using only the letters on one row of the keyboard.
Posted:
#
Quote: "Worldwide collection"Come on, we gave you the pointers how to start this, use google to help yourself.
Quote: "rsirian1"No it won't count the numbers in column T. What you need to do is on a new column use the formula =T3*AB3 assuming the count is in column AB like in your earlier picture. Select the cell and drag it down like before.Your method works, because I don't have many coins that are 15mm and smaller I counted them all manually and yes it is correct. But I do have an error (Pic below) about the how the command sorts the values.
Like this:
They say "Pecunia non olet", but I know better...
Posted:
#
Edited:
Edited:
Quote: "Worldwide collection"That's only an "error" on row 2 because it's trying to multiply the word Quantity (T2) and 0 (AB2). 0 X "Quantity" = ?. Ignore it or just delete that cell.
Posted:
#
Quote: "rsirian1"Ok thanks.
Quote: "Worldwide collection"That's only an "error" on row 2 because it's trying to multiply the word Quantity (T2) and 0 (AB2). 0 X "Quantity" = ?. Ignore it or just delete that cell.
Hi to whoever is reading this. Did you know that TYPEWRITER (on a QWERTY keyboard) is the longest word you can type using only the letters on one row of the keyboard.
Worldwide collection
Joined: 1 Mar 2020
Posts: 2253
Posts: 2253
Posted: 30 Nov 2021, 18:11
#
Edited: 30 Nov 2021, 18:27
Edited: 30 Nov 2021, 18:27
Status changed to Solved
(Worldwide collection, 30 Nov 2021, 18:11)
Status changed to Opened
(Worldwide collection, 30 Nov 2021, 18:27)
Posted:
#
Another error maybe due to rounding issues?
Hi to whoever is reading this. Did you know that TYPEWRITER (on a QWERTY keyboard) is the longest word you can type using only the letters on one row of the keyboard.
Posted:
#
The formula including AG cell is already in that self-same AG cell.
So it goes round and round and is called circular -
like the explanation note on the right.
Try using that same formula in a different unused cell.
If the AH cell is unused then type the formula in there ...
So a formula will not work in a cell if it includes that actual cell in itself.
Example a formula for A B C D cell must be typed elsewhere, in say E F and so on -
do not type a formula in a cell that refers to itself (making it 'circular'). Hope I explained it.
So it goes round and round and is called circular -
like the explanation note on the right.
Try using that same formula in a different unused cell.
If the AH cell is unused then type the formula in there ...
So a formula will not work in a cell if it includes that actual cell in itself.
Example a formula for A B C D cell must be typed elsewhere, in say E F and so on -
do not type a formula in a cell that refers to itself (making it 'circular'). Hope I explained it.
Token collector [1600-1899] with some coins
Posted:
#
Quote: "ZacUK" The formula including AG cell is already in that self-same AG cell.Ahh, silly me I forgot to change the formula to the previous box.
So it goes round and round and is called circular -
like the explanation note on the right.
Try using that same formula in a different unused cell.
If the AH cell is unused then type the formula in there ...
So a formula will not work in a cell if it includes that actual cell in itself.
Example a formula for A B C D cell must be typed elsewhere, in say E F and so on -
do not type a formula in a cell that refers to itself (making it 'circular'). Hope I explained it.
Hi to whoever is reading this. Did you know that TYPEWRITER (on a QWERTY keyboard) is the longest word you can type using only the letters on one row of the keyboard.
Posted:
#
With Excel = Diameter 20 + thickness 2
With OpenOffice = Diameter between 20 and 21
With LibreOffice = Diameter between 20 and 21 + thickness > 2
we can add: have only the squares, without the doubles, only one value, etc etc
good luck
With OpenOffice = Diameter between 20 and 21
With LibreOffice = Diameter between 20 and 21 + thickness > 2
we can add: have only the squares, without the doubles, only one value, etc etc
good luck
Posted:
#
Quote: "CREPOSUC"With Excel = Diameter 20 + thickness 2Sorry I don't really understand what you mean. I assume your talking about adding 2mm to the total diameter or thickness of each coin?
With OpenOffice = Diameter between 20 and 21
With LibreOffice = Diameter between 20 and 21 + thickness > 2
we can add: have only the squares, without the doubles, only one value, etc etc
good luck
Hi to whoever is reading this. Did you know that TYPEWRITER (on a QWERTY keyboard) is the longest word you can type using only the letters on one row of the keyboard.
Status changed to Solved
(Worldwide collection, 4 Dec 2021, 16:55)
Posted:
#
Quote: "ferluq"I'm glad it helped!
Quote: "leopiccionia"Perhaps it's off-topic, but I'd like to give a tip. The formula that works better for me, when using flips, is diameter + (thickness / 2), always rounding up (ceiling).Just wanted to say thank you to leopiccionia for this formula. I worked perfectly to decide which cardboard holder to use.
For example, if a coin has 24.5 mm of diameter and 1.2 mm of thickness, I'd use a 27.5 mm flip, not a 25 mm flip (24.5 + 0.6 = 25.1).
Cheers!
Used time zone is UTC+2:00.
Current time is 17:22.