Multi-Row Formula: Malformed if Statement

[Amy_2020]

Hello - I'm trying to utilize the Multi-row formula to solve my problem, but I don't know that it will get me there.  Any alternative suggestions I'm open to!  Essentially trying to combine the values from two separate columns across multiple rows of data, if certain criteria are met.  A basic example is shown below.  For each row in a sequence, I need to combine the value of [Row1: Weekend Hours] + [Row+1: Lag Hours] and place the new value in the "Total Time Less Weekends" column.  Sometimes there may be a null value in either column, the same calculation should apply.  

Another caveat is for each row 1-7 above, the data is assigned to one Document.  My data set has thousands of documents, each with anywhere from 4 - 20+ rows.  The first row (logid) of each document should have a Lag Hours value of 0, so the formula I have that is returning the error looks like the following....

iif([logid]='00001', [Lag_hrs], ([Lag_hrs]+[Row-1:Weekend Hours]),'hour')

This may be confusing, so happy to provide more details where needed!

[AbhilashR · Accepted solution]

Hi @Amy_2020, could you give the attached solution a try and let us know if this addresses your ask?

Below is the formula I wrote to replicate your output

IF [RecordID] = 1 
	THEN [Lag Hours]
ELSE TONUMBER([Lag Hours]) + TONUMBER([Row-1:Weekend Hours])
ENDIF

[Amy_2020]

It did get rid of the Malformed if Statement, but is showing this error instead:

[ELOCKER]

change your data type or update the formula to be    = '1'.        You probably have the ID field as a text value.

[Amy_2020]

That did it!!! Thank you both! 

Once last piece I've been trying to tie together....I'm doing a simple weekend calculation currently to capture my weekend hours.  To capture non-working hours, however, I should be capturing an additional 7 hours each for Fri evening and Mon morning.  Is this a "Formula" join that can be added for any time my "# of Weekend Days" has a multiple of 2, return a value of (7 * "# of Weekend Days")?  I also need to add a flat 7 hours if the value is a "1"

This is my table currently, so I believe I would need to add a column for "Weekend Days with Buffer" to have the additional hours reflected?

[AbhilashR]

Sounds like an If-Else statement within a Formula tool should help you get your desired output

[ELOCKER]

 

I cant see your data, but try this.

[Amy_2020]

Thank you! I got there.  Final piece....I need to identify where the sum of the "Lag Hours Less Weekends" by the same Doc ID and Role has a value <48 should return a "Yes", and where the sum of role values is >48 should return a "No".   Sample data is below.  The PO_AP lines should be Yes, since total is 6, the PO_Freight should be No in each row since the total sum is 80...

This is the multi-row formula I have that's returning an error, but not I'm using the correct function?  This is grouped by Doc ID and Role...