You are viewing limited content. For full access, please sign in.

Question

Question

FIXED() function proper use?

Updated August 30, 2017
asked on August 30, 2017

I am trying to fix values at the end of each row so that they wont be affected once adding values to another row.

I am wondering if i should use the FIXED() function and if so what is the correct way to use it.

any links to documentation on the new formulas (Fixed, Choose, Proper, etc.) that have been added to forms designer would be greatly appreciated also.

0 0

Answer

SELECTED ANSWER
replied on August 30, 2017

Good morning Dean,

This link provides the most recent specifications that I can find for the OpenFormula standard used by Forms calculations (and MS Excel, I believe).

http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html

Since Microsoft also uses the OpenFormula standard, you might find their documentation useful:

https://support.office.com/en-us/article/FIXED-function-ffd5723c-324c-45e9-8b96-e41be2a8274a

I'm happy to help you figure out the best tools for the goal you want to accomplish, but I need some clarification re: your first paragraph: are you working with a table? Can you provide screenshots?

~Rob

1 0
replied on August 30, 2017

I am working with a table within a form and a database. 

The goal is to calculate the expenses for a mileage form.

 

when name is entered, i have lookup rules in place to populate: department, engine size, band rate, db kilometers, band 1-4.

DB + total kilometers is the combined running total as values are added to tables kilometers.

In the preview screenshot, the total kilometers is set to hit over 1500 which will swap the band rate to band 2.

above, the total km has exceeded 1500, the band rate has switched, but it has updated the rows which were already calculated. I need the first 1500 kilometers to be calculated at 0.3986 and then above 1500 to be calculated at 0.7321 and then added together in reimbursement.

This is my attempted IF statement within reimbursement to calculated each row.

Hope this is enough information provided! 

0 0
replied on August 30, 2017

Hi Dean,

Okay, thanks for the information and screenshots! What I would suggest is using JavaScript to perform the calculations and update the relevant fields that way--in my experience, lots of nested calculations can cause headaches, and using them on (multi-row) tables does not usually achieve the desired results.

Unfortunately I do not have the time right now to prepare a snippet, but if I get a little time tomorrow, I'll try to post back with a solution.

Good luck!

~Rob

1 0

Replies

You are not allowed to reply in this post.
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...