![]() |
#1 |
Member
![]() Posts: 14
Karma: 10
Join Date: Dec 2016
Location: United Kingdom
Device: Amazon Kindle PaperWhite 3
|
![]() ![]() Is it possible to have a formula in a custom column that performs an arithmetic operation on the values of two other columns? For example, I have a custom column (#words) that contains the number of words in each book, and another custom column (#pages) that contains the number of pages in each book. I would like to have another custom column that shows the average number of words per page, so it would divide #words by #pages, provided both #words and #pages contain positive integer values. How might I achieve this? |
![]() |
![]() |
![]() |
#2 |
null operator (he/him)
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() Posts: 20,771
Karma: 27405072
Join Date: Mar 2012
Location: Sydney Australia
Device: none
|
See Arithmetic functions in Template language - Function classification
There's sticky at the top of this sub-forum that might have some examples of usage. And searches for 'arithmetic', 'math' and 'calculate' should find some more. If your #words and #pages columns originate from the Count Pages plugin you could create a private version of the PI and do the calculate in there - in python. Plugin zips contain the source code. BR |
![]() |
![]() |
![]() |
#3 | |
Member
![]() Posts: 14
Karma: 10
Join Date: Dec 2016
Location: United Kingdom
Device: Amazon Kindle PaperWhite 3
|
Quote:
![]() I have almost got this working now, though I still have a couple of issues to iron out, and any help you might offer will be appreciated. First, in order to ensure that I do not attempt the division if either #words or #pages have null or zero values, I first created a temporary custom column called #ready, which has the following template: Code:
{:'and(field('#words'), strcmp(field('#words'), "", "", "", cmp(field('#words'), 0, "", "", field('#words'))), field('#pages'), strcmp(field('#pages'), "", "", "", cmp(field('#pages'), 0, "", "", field('#pages'))))'} Using #ready as a base, I then created another custom field to carry out the division and called it #awp (average words per page). This field has the template: Code:
{:'test(and(field('#words'), strcmp(field('#words'), "", "", "", cmp(field('#words'), 0, "", "", field('#words'))), field('#pages'), strcmp(field('#pages'), "", "", "", cmp(field('#pages'), 0, "", "", field('#pages')))), divide(field('#words'), field('#pages')), "")'} The only time it doesn't work is when both #words and #pages contain null values. In this case, I get the following error message: Code:
EXCEPTION: float division by zero |
|
![]() |
![]() |
![]() |
#4 |
Member
![]() Posts: 14
Karma: 10
Join Date: Dec 2016
Location: United Kingdom
Device: Amazon Kindle PaperWhite 3
|
Yes, both of my #words and #pages columns originate from the Count Pages plugin. I appreciate your suggestion regarding creating a private version of the plugin, however, my proficiency with Python is not yet up to the task.
|
![]() |
![]() |
![]() |
#5 |
null operator (he/him)
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() Posts: 20,771
Karma: 27405072
Join Date: Mar 2012
Location: Sydney Australia
Device: none
|
@Soap-dodger - I always have to hack templates by trial and error, so I won't try to answer your questions but I would have tried to use a Program Mode template. BTW the ifempty function will test for Undefined (null).
Hopefully one of the template experts, such as chaley, davidfor will chime in. BR |
![]() |
![]() |
![]() |
#6 | |
Grand Sorcerer
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() Posts: 24,905
Karma: 47303822
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos
|
Quote:
![]() But... If it isn't obvious, I usually do it in program mode first. This is what I got: Code:
program: pages = cmp(raw_field('#pages'),0,1,1,raw_field('#pages')); words = cmp(raw_field('#pages'),0,0,0,raw_field('#words')); divide(words,pages); In non-program mode this becomes: Code:
{:'divide(cmp(raw_field("#pages"),0,0,0,raw_field("#words")),cmp(raw_field("#pages"),0,1,1,raw_field("#pages")))'} Code:
{#pages:'test($,divide(raw_field("#words"),raw_field("#pages")),0)'} To get an integer, you can use "format_number" with an appropriate format template. |
|
![]() |
![]() |
![]() |
#7 | |
Guru
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() Posts: 950
Karma: 1183425
Join Date: Dec 2016
Location: Goiânia - Brazil
Device: iPad, Kindle Paperwhite
|
Quote:
![]() Code:
program: pages = cmp(raw_field('#pages'),0,1,1,raw_field('#pages')); words = cmp(raw_field('#pages'),0,0,0,raw_field('#words')); wpp = format_number(divide(words,pages),'{0:.0f}'); cmp(wpp, 0, '', '', wpp) Last edited by thiago.eec; 06-25-2019 at 03:07 PM. |
|
![]() |
![]() |
![]() |
#8 |
Member
![]() Posts: 14
Karma: 10
Join Date: Dec 2016
Location: United Kingdom
Device: Amazon Kindle PaperWhite 3
|
|
![]() |
![]() |
![]() |
#9 | |
Member
![]() Posts: 14
Karma: 10
Join Date: Dec 2016
Location: United Kingdom
Device: Amazon Kindle PaperWhite 3
|
Quote:
![]() I am having difficulty in converting the result to an integer. I have read the relevant section of the Calibre manual to no avail. Here's what I've tried so far: Code:
format_number(divide(words,pages), {0:3.0f}); format_number(divide(words,pages), "{0:3.0f}"); format_number(divide(words,pages), {0:,d}); format_number(divide(words,pages), "{0:,d}"); Please, can you advise where I've got it wrong? |
|
![]() |
![]() |
![]() |
#10 | |
Grand Sorcerer
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() Posts: 24,905
Karma: 47303822
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos
|
Quote:
Code:
format_number(divide(words,pages), "{0:3.0f}"); Code:
{:'format_number(divide(cmp(raw_field("#pages"),0,0,0,raw_field("#words")),cmp(raw_field("#pages"),0,1,1,raw_field("#pages"))), "3.0f")'} |
|
![]() |
![]() |
![]() |
#11 | |
Member
![]() Posts: 14
Karma: 10
Join Date: Dec 2016
Location: United Kingdom
Device: Amazon Kindle PaperWhite 3
|
Quote:
![]() |
|
![]() |
![]() |
![]() |
Tags |
arithmetic, custom column |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
Adding custom column with any lookup-name gives me empty column as a result? | Ahu Lee | Library Management | 16 | 06-09-2019 12:14 PM |
Move selected data from series column to a new custom column | fvdham | Library Management | 3 | 06-02-2017 03:49 PM |
Custom column returns value based on value of another custom column? | calvin-c | Calibre | 3 | 09-14-2013 02:24 PM |
Custom yes/no column built from long text column | Philantrop | Library Management | 7 | 03-23-2013 07:44 PM |
how to move value(s) of tag column to a custom made column | zoorakhan | Library Management | 0 | 12-08-2012 03:53 AM |