クラシックファイルタイプは、2021年1月をもって使用不可となりました。クラシックファイルを移行するか、PDFをダウンロードすることができます。 詳細を見る

Formulas in Spreadsheet

デモをリクエスト
  • 編集済み

Hi

Does Workiva has a functionality to evaluate a formula error in spreadsheet just like excel in case to understand which parameter is failing and another thing does Workiva doesn't accept this kind of multiple criteria formula as the same formula  when written in excel is giving the correct result but Workiva is throwing #VALUE!. Is there any other way this formula can be written in Workiva.

The one highlighted in Bold is the parameter which is failing as rest of the formula is working as expected when broken down for evaluation.

SUM(SUMIFS(INDEX('Amount'!$A:$J,0,MATCH($O$10,'Amount'!$A$1:$J$1,0)),INDEX('Amount'!$A:$J,0,MATCH($N$9,'Amount'!$A$1:$J$1,0)),$O$9,INDEX('Amount'!$A:$J,0,MATCH($N$8,'Amount'!$A$1:$J$1,0)),"<="&$O$8,INDEX('Amount'!$A:$J,0,MATCH($J14,'Amount'!$A$1:$J$1,0)),$L14,INDEX('Amount'!$A:$J,0,MATCH($N$5,' Amount'!$A$1:$J$1,0)),$O$5,INDEX('Amount'!$A:$J,0,MATCH($N$6,'Amount'!$A$1:$J$1,0)),{"123","1234"})))*1

 

Thanks & Regards,

Dipti Khandelwal
0

コメント

2件のコメント

日付 投票
  • Mike

    Hi Dipit!

    Thanks for the detailed question. I am happy to help answer this one for ya.
     
    Currently Workiva do not have an evaluate formula feature that digs into errors. If there is an error, though, there should be description of the error available if you hover over the red formula indicator triangle in the bottom right of the cell, though it doesn't identify exactly where the issue is in your syntax.
     
    Regarding your formula, it seems the issue is it not liking the brackets. I'm not overly familiar with the use of brackets around two text inputs. Can you tell me what the values in the brackets, i.e. 123, 1234, are meant to do in the formula?
     
    0
  • Dipti Khandelwal
    • 編集済み

    Hi Mike,

    Thanks for the response !

    To answer your question the  brackets, i.e. 123, 1234 will act like an OR function where if both the parameters i.e. 123 and 1234 is having values the final output will give be the sum of those two otherwise respective values will be the final output.

    Just to add in more I can break the bracket condition by adding the same formula breaking those 2 parameters by just a sum but the formula will become very lengthy so just wanted to check if by any chance the above formula can work with the correct brackets.

     

    Thanks & Regards,

    Dipti Khandelwal

    0

サインインしてコメントを残してください。