Custom Functions
Post any examples of custom functions in this forum
Removing " from a string
Forums
Hi, How do I remove " from a string. If I use " " " , it shows an error as Invalid parameter.
- Read more about Removing " from a string
- 1 comment
- Log in or register to post comments
My first custom function--the haversine formula
Forums
Hi
my first post and a chance to say how useful i have found this site and the linked videos
I have recently been tasked with testing how far customers laddresses are from the business. I found a data base which had the longitude and latitude of every UK postcode, armed with that information i came up with this custom function
Option ExplicitFunction getDistance(latitude1 As Double,longitude1 As Double,latitude2 As Double,longitude2 As Double) As DoubleDim earth_radius As DoubleDim Pi As DoubleDim deg2rad As Double
STATISTICS
Forums
Good afternoon, dear.
How to calculate the median of a group of data in IDEA.
- Read more about STATISTICS
- 4 comments
- Log in or register to post comments
Function to generate identical numbering for each document number
Forums
Hello Everyone,
I'm trying to create an equation in order generate an identical number for each document number included in a database. At the same time for next document number the numbering have also to be ascending. I try to better explain the situation in the attachment.
Many thanks to those who will help me.
Angelo
Function to generate restarting sequential number for each document number
Forums
Hello Everyone,
It's my first time in this wonderful IDEA forum.
I'm blocked in my project since I cannot found a function (or other way) to create a sequential number that restart every time the document number changes. In attachment, a screenshot to better understanding.
So,I want that for document number "1310000000" in the column "POSTING_NUMBER" there will be "1" for all related lines; for document number "13100000001" in the column "POSTING_NUMBER" there will be "2" for all lines; and so in an ascending way for next document numbers.
RegEx
Forums
HI,
I have a string like this:
AR 2016 INTREO Office roll-out - Preliminary Study
and I need to match the characters only having this result
AR INTREO Office roll out Preliminary Study
I have tried with
@RegExpr(CLIENT,"([^0-9-]+$") returns INTREO Office roll-out - Preliminary Study
and
@RegExpr(CLIENT,"([^0-9-]+") returns AR
I have also to combine the other expression with "OR" operator but did not work.
- Read more about RegEx
- 3 comments
- Log in or register to post comments
Apply formula cell by cell
Forums
I am trying to standardise a file like this:
Blank.jpg
I need to all the cell filled for further grouping.
I have tried with this formula:
@IF(MAIN_AGGREGATION == "",@GetPreviousValue("MAIN_AGGREGATION"),MAIN_AGGREGATION)
Unfortunately it works for the first 2 rows only of Column AAA.
How I can get it work as if IDEA creates a virtual value to look up to give fill the blank cells?
- Read more about Apply formula cell by cell
- 2 comments
- Log in or register to post comments
point comma
Forums
Hi,
I'm looking for a formula what changes a character field (which includes digits with point "." for decimal number) to a numeric field with a comma "," (french presentation of numbers). I've tested @curval but I didn't suceed to have two digits after the comma. I don't understand why. Do you know a solution ?
Thanks in advance !
- Read more about point comma
- 7 comments
- Log in or register to post comments
Rounding date forward
Forums
Hi,
I am looking to add a number of days to a given date to calculate an eligible date then round it to the first of the following month to get an entry date.
DOH: 3/5/2001
Eligible Date: 6/3/2001
Entry Date: 7/1/2001
The eligible date does not need to be an output, just the entry date.
I currently have:
@daystod(@DToDays(DOH)+90)
this is giving me the correct elibile date, but I cannot figure out how to round it to the first of the next month.
thanks!
- Read more about Rounding date forward
- 1 comment
- Log in or register to post comments
dateParm
Forums
Hi,
I am attempting to create two "dateParm"s without having to actually type both in an input box. DateParm will be an inputbox (example, i type in "20181127")
DateParm1 should always be 4 days prior to "dateParm" . How can i create a custom formula to make DateParm1 = dateParm - 4 days.
Sub Main
- Read more about dateParm
- 4 comments
- Log in or register to post comments