NULL CELL NOT SUPPLYING THE VALUE

Hi guys,

I am a kind of confuse because I have created a formula but the formula is not working. I am trying to supply the NULL value at column % but nothing is working.

Can you please help for the correct formula? See below snapshots for your reference.

Hope to hear from anyone.

Kamen

Workflow

Reporting

Common Use Cases

Accepted answers

All comments

CoG

Are you trying to refer to other rows/records? The scope of the formula tool is a single row. So based on your formula, that last row IsNull([%]) is true and so the value is updated with [SumPercent2], which is also null. Therefore nothing is going to change. Please include what your desired output is, so that more specific help can be provided.

KamenRider

Hi @CoG

I like the 99.55% (sumpercent2) be on the last column of field % which looks like this:

Please let me know if you still have questions. Thanks and hope to hear from you.

Kamen

apathetichell

I think your formula needs to be if [SumPercent2]=null() then null() elseif [%] =null() then [SumPercent2] else [%] endif

basically your formula is saying is % isn't null - keep it as % - but perhaps you are looking to replace that value as null() if sumpercent2 is null?

KamenRider

HI @apathetichell

Actually you are right, if the % is not NULL, it will give whatever value it have and if null it will get the value from the SumPercent2. The reason because I am getting some sort of Total at the end of the row. I use the summarize tool to get the total. Below is the raw data from the workflow:

I need to put the SumPercent2 on the last row under % since it will represent its total.

Please help me on this.

Kamen

apathetichell

multi-row formula - modify the field %

if [row+1:%]=null() then [row-1:SumPercent2] else [%] endif

or something like that. you'll also want to set values that don't exist as equal to null

KamenRider

Hi @apathetichell

Thanks for the response. I use the formula you gave but it replace my zero% instead it should go or falls to the 5th rows which for the totals. Can you please update the formula you gave?

Hoping for your response. thanks.

Kamen

apathetichell

or something like that. you'll also want to set values that don't exist as equal to null - right now you have it at 0.

KamenRider

Sorry @apathetichell , what do u mean.. can you please elaborate? What will be the correct formula?

Hope to hear from you.

Thanks,

Kamen

KamenRider

hI @apathetichell

I think a get what you mean. however result still show that the zero has been still replaced by the total percentage 99.55%. Can you help me how to retain the zero?

Please advise.

Kamen

apathetichell

oh! yes - I see what you are saying. change it from referencing [%] to [TOTAL] in the row+1 section - so [Row+1:TOTAL] - the logic here is you want to find the column which has not value in [Row+1] because it does not exsist. so you want that to reference something that MUST have a value in every row. [%] was wrong to reference.

KamenRider

Hi @apathetichell

Jus wondering, if that the case, what will be the correct formula. I don't use much of this type of tool since I find it complicated. Any ideas on how I am going to solve my dilemma?

Please advise.

Kamen

anandakrishnan

@KamenRider Can please share the data set, will help you with workflow. What i fell is an "if condition" can solve this issue.

CoG

Alternatively, and maybe a more robust process, if you move the Multi Row Formula Tool to before the Formula Tool that was showcased in your OP, you can instead modify the SumPercent2 Field with the following Multi Row Formula:

IIF(IsNull([SumPercent2]),[Row-1:SumPercent2],[SumPercent2])

After this, the formula you had originally will have something to pull from and should work. (NOTE: Just make sure the data is sorted in such a way that the nulls in [SumPercent2] are not in the top row of the data)

Hope this helps and Happy Solving!

KamenRider

Hi @CoG @anandakrishnan @apathetichell

I've created a sample file that will act an input file. Below is the sample file.

For your understanding, the output file should look like this.

I hope guys you could help me create the output required for me. I've also attached the excel file.

Looking forward for your response.

Kamen

Sample Compute Metric.xlsx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703