Calculating the weighted average

[Nikiska]

Hiya, I am fairly new to Airtable and have been tasked with setting up our CRM for the charity (HUGE JOB!) 

So... we have to get our dogs weighed roughly every month, but I need to be able to compare current weight with previous weight and how much the weight has either gone up or down. What I would like is when I come to enter a new current weight (in the {Current Weight kg} & {CW Date}(which is automatic date), the data that is already in those fields move to the {Previous weight} column and {PW Date} column (currently I am copying and pasting the data from current columns to previous columns before I enter the new current weight which is not ideal!.

I then would like to merge my two formula columns {Difference} & {Difference in weight} into one formula so that all it basically shows is the weight difference and either a colorful up arrow or a down arrow emoji.... (oh and have the blank fields remain blank (not saying up!!) 

Can anyone help me do this?

@Ben_Young1 

[Jake_Wilson]

Hey @Nikiska.  I'm pretty sure this can only be accomplished with some scripting.  I can help you with the script, it shouldn't be too bad.  Would you mind sharing some test data with me so I can write and test the script for you?

[pressGO_design]

Your up/down column needs an IF() statement that checks to see if there’s anything in Previous weight before doing the calculation that results in up/down. 

IF(
AND(
{Previous weight}, {Previous weight}-{Current weight}<0
),
“up”,
IF(
AND(
Previous weight}, {Previous weight}-{Current weight}>0
),
“down”,
“”
)

As far as eliminating copy/paste, I would add a checkbox field called Enter New Weight that triggers an Update Record automation that copies the data from the current weight and date to the previous weight and date, clears the current weight and date and resets the checkmark to unchecked. But but but but if you need to keep a record of previous weights this will not work. 

[Ben_Young1]

Hey @Nikiska!

Just letting you know that I’ve seen this! I’m going to take some time to play around with this and put together some solutions for you.

[pressGO_design]

Oops I haven’t had enough coffee. The formula to just have the weight difference and emoji is

IF({Previous weight}, {Previous weight}-{Current weight})& IF({Previous weight}-{Current weight}>0, “ ⬇️”, IF({Previous weight}-{Current weight}<0, “ ⬆️”, “ 🟰”)

[Nikiska]

Thank you 🙂

[Nikiska]

Sure - I have screenshot the actual airtable table in my question, but attached is a couple of rows in an excel sheet (no formulas, just figures)

[Nikiska]

Thank you, those emoji's work great, I now need to add the actual weight difference into your formula somehow!! 😁 So the column will read -0.75kg ⬇️ ;   or 0.9kg ⬆️  ; etc etc!

This is what I am currently using to get the difference in weight to 2 decimal places:

ROUNDUP(FLOOR(SUM({Current Weight (kg)}-{Previous weight}),0.001),2)& "kg"

[Jake_Wilson · Accepted solution]

@Nikiska  I made a base + script for you.  I believe this will do exactly what you're asking for.  

Watch the loom video below for a 60 sec demo

https://www.loom.com/share/9f17f57c72e14ad5be67d04a19e83382?sid=402c3700-310b-4840-a019-025149e885e1

Copy this base to get the script

https://airtable.com/appUyycRiK3Eum1z3/shrALRM0ndztSAHCm

[pressGO_design]

I’m confused. You have weights entered to 1 decimal point, but you want the weight differences to be to 2 decimal points? I think I’m missing something…