IF Function with #VALUE! error - Formula/scoring matrix Help!

, ,
1

Hello,

I am trying to create a “scoring matrix” of sorts using IF formulas, and not sure what exactly I am doing wrong.

I have a field with field code “seniorstatus” and the Drop-down field allows for two responses “yes” and “no.”

I have a Calculated field with field code “SeniorStatusPoint.”

I input the following formula into the Calculated field that reads:

IF(CONTAINS(seniorstatus,"Yes"),"1","0")

However, it results in the #VALUE! error.

What am I doing wrong?

Also, I want to make a formula that assigns a point based on the age of the record.

I have the age of record formula set up and working at this time.

It is just the next formula I am not sure what to do.

Record 1 year old = 1 point

Record 2 years old = 2 points

Record 3 years old = 3 points

Record 4 years old = 4 points

Etc…

Please, any help you can provide would be great!!!

-Riot

2

Hi @LHunter

I am trying to create a "scoring matrix" of sorts using IF formulas, and not sure what exactly I am doing wrong.

If the Drop-down field only has a "yes" or a "no" option, could you change that to a Check box field?

The CONTAINS function works for Check Boxes and Multi-Choices.

Also, for the formula that gets inputted to a Calculated field, it should look like the following,

IF(CONTAINS(seniorstatus,"Yes"),1,0)

Please refer to the following help page article for more details on this.

CONTAINS Function: Calculation using Check Boxes and Multi-Choices:

https://get.kintone.help/k/en/id/040506.html

For your second question,

Also, I want to make a formula that assigns a point based on the age of the record.

Could you kindly elaborate on this?

Which field are you using or trying to use for "age of the record"?

Please correct me if I am wrong, but I am assuming that a user selects an option from a field, and you would like to display the points according to the selection.

If possible, some screenshots of the form/field you are using and if you could share the formula you have might be helpful.

I hope my first half reply answers your first question.

1 Like
3

Hello, thank you so much.

IF(CONTAINS(seniorstatus,"Yes"),1,0)

This worked for the IF functions!

Overall Project - Waitlist App with Scoring

The overall project that I am working on is a waitlist that scores the applicant based on a series of Yes/No questions and the overall age of the application (a record).

  • For the YES/NO questions, the points are Yes= 1 point and No= 0 Point.
  • For age, I want to assign a point based on how long they are on the waitlist.
    • Example: For every 30 days that they are on the waitlist, they get 0.25 points. Or a value of points per day that they are on the list.

Then all the points will be tallied for the application's final score.

Finally, the applications (records) would be sorted with the highest scoring on the top.

For my age of application, I have it set up as follows:

  • An application Date and Time field - This will be the application's submission date.
  • For the application age, I have a Calculated field with the following formula: WaitlistUpdate_Date_and_time-Application_Date_and_time

I have attached screenshots of the two form fields I have set up for the age of application portion:

Kintone_Settings
Kintone_Settings1420×831 103 KB

Thank you for your help!

4

Hello @LHunter ,

In Kintone, the values in the date, time, and date and time fields are treated as UNIX time. That means the dates and the times are treated in seconds. In other words, you need to specify them in seconds in the formulas. You will first need to convert your date fields into days so you can use them to calculate the points.

Please refer to the following page on how to use the calculated field to calculate the number of days:

E.g.
(WaitlistUpdate_Date_and_time - Application_Date_and_time) / (60*60*24)

1 Like