Skip to content
TheChatArchiveThe Chat Archive
grok-3-mini-high· xAIHugging Face LMArena Human Preference 140K

I have this formula in my model:…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Metallama-4-maverick-03-26-experimental’s answer.
  1. User

    I have this formula in my model: =IF(CC113="Equally",(I65+I68+I71+I74+I77+I80+I83+I86+I89+I92+I95+I98+I101+I104+I107+I110)/16,IF(CC113="Confidence",(I65I66+I68I69+I71I72+I74I75+I77I78+I80I81+I83I84+I86I87+I89I90+I92I93+I95I96+I98I99+I101I102+I104I105+I107I108+I110I111)/16,IF(CC113="Events",(I65VALUE(TRIM(RIGHT(SUBSTITUTE(C66,"&",REPT(" ",100)),100)))+I68*VALUE(TRIM(RIGHT(SUBSTITUTE(C69,"&",REPT(" ",100)),100)))+I71VALUE(TRIM(RIGHT(SUBSTITUTE(C72,"&",REPT(" ",100)),100)))+I74*VALUE(TRIM(RIGHT(SUBSTITUTE(C75,"&",REPT(" ",100)),100)))+I77VALUE(TRIM(RIGHT(SUBSTITUTE(C78,"&",REPT(" ",100)),100)))+I80*VALUE(TRIM(RIGHT(SUBSTITUTE(C81,"&",REPT(" ",100)),100)))+I83VALUE(TRIM(RIGHT(SUBSTITUTE(C84,"&",REPT(" ",100)),100)))+I86*VALUE(TRIM(RIGHT(SUBSTITUTE(C87,"&",REPT(" ",100)),100)))+I89VALUE(TRIM(RIGHT(SUBSTITUTE(C90,"&",REPT(" ",100)),100)))+I92*VALUE(TRIM(RIGHT(SUBSTITUTE(C93,"&",REPT(" ",100)),100)))+I95VALUE(TRIM(RIGHT(SUBSTITUTE(C96,"&",REPT(" ",100)),100)))+I98*VALUE(TRIM(RIGHT(SUBSTITUTE(C99,"&",REPT(" ",100)),100)))+I101VALUE(TRIM(RIGHT(SUBSTITUTE(C102,"&",REPT(" ",100)),100)))+I104*VALUE(TRIM(RIGHT(SUBSTITUTE(C105,"&",REPT(" ",100)),100)))+I107VALUE(TRIM(RIGHT(SUBSTITUTE(C108,"&",REPT(" ",100)),100)))+I110*VALUE(TRIM(RIGHT(SUBSTITUTE(C111,"&",REPT(" ",100)),100))))/SUMPRODUCT(N(IFERROR(VALUE(TRIM(RIGHT(SUBSTITUTE(CC66:CC111,"&",REPT(" ",100)),100))),0))),IF(CC113="Confidence & Events",(I65I66VALUE(TRIM(RIGHT(SUBSTITUTE(C66,"&",REPT(" ",100)),100)))+I68*I69*VALUE(TRIM(RIGHT(SUBSTITUTE(C69,"&",REPT(" ",100)),100)))+I71I72VALUE(TRIM(RIGHT(SUBSTITUTE(C72,"&",REPT(" ",100)),100)))+I74*I75*VALUE(TRIM(RIGHT(SUBSTITUTE(C75,"&",REPT(" ",100)),100)))+I77I78VALUE(TRIM(RIGHT(SUBSTITUTE(C78,"&",REPT(" ",100)),100)))+I80*I81*VALUE(TRIM(RIGHT(SUBSTITUTE(C81,"&",REPT(" ",100)),100)))+I83I84VALUE(TRIM(RIGHT(SUBSTITUTE(C84,"&",REPT(" ",100)),100)))+I86*I87*VALUE(TRIM(RIGHT(SUBSTITUTE(C87,"&",REPT(" ",100)),100)))+I89I90VALUE(TRIM(RIGHT(SUBSTITUTE(C90,"&",REPT(" ",100)),100)))+I92*I93*VALUE(TRIM(RIGHT(SUBSTITUTE(C93,"&",REPT(" ",100)),100)))+I95I96VALUE(TRIM(RIGHT(SUBSTITUTE(C96,"&",REPT(" ",100)),100)))+I98*I99*VALUE(TRIM(RIGHT(SUBSTITUTE(C99,"&",REPT(" ",100)),100)))+I101I102VALUE(TRIM(RIGHT(SUBSTITUTE(C102,"&",REPT(" ",100)),100)))+I104*I105*VALUE(TRIM(RIGHT(SUBSTITUTE(C105,"&",REPT(" ",100)),100)))+I107I108VALUE(TRIM(RIGHT(SUBSTITUTE(C108,"&",REPT(" ",100)),100)))+I110*I111*VALUE(TRIM(RIGHT(SUBSTITUTE(C111,"&",REPT(" ",100)),100))))/SUMPRODUCT(N(IFERROR(VALUE(TRIM(RIGHT(SUBSTITUTE(CC66:CC111,"&",REPT(" ",100)),100))),0))),"")))). and it is returning an error value due to some target cells being words instead of numbers, can you amend the formula to be word proof and still carry on the calculation whenever there are numbers?

  2. Assistant

    The error in your formula is likely due to non-numeric values (text or errors) in the referenced cells, which cause functions like arithmetic operations or VALUE() to return errors. To make the formula "word-proof," I've modified it to handle non-numeric cells by treating them as zero. This is achieved by using IF(ISNUMBER(), ,0) to check if a cell contains a number and return 0 if it does not.

    Additionally, I've used SUMPRODUCT with array handling to simplify the summation over the non-contiguous ranges (e.g., every 3rd row) and incorporated conditions to ensure only the intended cells are processed. This approach also adds error handling for the weight extraction in the "Events" and "Confidence & Events" cases.

    Here's the amended formula:

    =IF($C$113="Equally", SUMPRODUCT(--(MOD(ROW($I$65:$I$110)-65,3)=0), IF(ISNUMBER($I$65:$I$110), $I$65:$I$110, 0)) / 16, 
    IF($C$113="Confidence", SUMPRODUCT(--(MOD(ROW($I$65:$I$110)-65,3)=0), IF(ISNUMBER($I$65:$I$110), $I$65:$I$110, 0), IF(ISNUMBER($I$66:$I$111), $I$66:$I$111, 0)) / 16, 
    IF($C$113="Events", SUMPRODUCT(--(MOD(ROW($I$65:$I$110)-65,3)=0), IF(ISNUMBER($I$65:$I$110), $I$65:$I$110, 0), IFERROR(VALUE(TRIM(RIGHT(SUBSTITUTE($C$66:$C$111,"&",REPT(" ",100)),100))),0)) / SUMPRODUCT(--(MOD(ROW($C$66:$C$111)-66,3)=0), IFERROR(VALUE(TRIM(RIGHT(SUBSTITUTE($C$66:$C$111,"&",REPT(" ",100)),100))),0)), 
    IF($C$113="Confidence & Events", SUMPRODUCT(--(MOD(ROW($I$65:$I$110)-65,3)=0), IF(ISNUMBER($I$65:$I$110), $I$65:$I$110, 0), IF(ISNUMBER($I$66:$I$111), $I$66:$I$111, 0), IFERROR(VALUE(TRIM(RIGHT(SUBSTITUTE($C$66:$C$111,"&",REPT(" ",100)),100))),0)) / SUMPRODUCT(--(MOD(ROW($C$66:$C$111)-66,3)=0), IFERROR(VALUE(TRIM(RIGHT(SUBSTITUTE($C$66:$C$111,"&",REPT(" ",100)),100))),0)), 
    ""))))
    

    Key Changes and Explanations:

    • Error Handling for Numeric Cells:

      • IF(ISNUMBER(range), range, 0) is used for all references to cells in columns I, J, etc., to return the numeric value if the cell contains a number, or 0 if it contains text or an error. This prevents errors during arithmetic operations.
    • SUMPRODUCT for Efficient Summation:

      • SUMPRODUCT is used with a condition --(MOD(ROW(range)-start_row,3)=0) to sum only the cells at the correct rows (e.g., every 3rd row starting from row 65 for column I). This handles the non-contiguous pattern without listing each cell individually.
      • For example:
        • In "Equally", it sums only the values in I65, I68, ..., I110.
        • In "Confidence", it sums the products I65I66, I68I69, ..., I110*I111.
        • In "Events" and "Confidence & Events", it handles the weighted sums with the correct pairings.
    • Weight Extraction Handling:

      • The weight extraction (e.g., VALUE(TRIM(RIGHT(SUBSTITUTE(C_cell,"&",REPT(" ",100)),100)))) already includes IFERROR to handle non-numeric values, which is retained. This ensures that if the extracted value is not a number, it is treated as 0.
      • A condition is added to the denominator and numerators in "Events" and "Confidence & Events" to sum only the intended weight cells (C66, C69, ..., C111), reducing the chance of including unintended data.
    • Division and Averaging:

      • Division by 16 or the sum of weights is handled as before, but now with error-proof numerators and denominators.
      • If the denominator (sum of weights) is zero, the formula might still return a #DIV/0! error. You could add additional error handling (e.g., IF(denominator=0, 0, calculation)) if needed, but it wasn't specified in your query.

    How to Use This Formula:

    • Copy and paste the formula into the desired cell in Excel.
    • Ensure your Excel version supports array formulas and SUMPRODUCT with multiple arrays (most modern versions do, and it should work without needing Ctrl+Shift+Enter).
    • Test the formula with sample data containing text to confirm it handles non-numeric values correctly by ignoring them (treating as zero).

    This amended formula should now be robust against text or errors in the target cells while maintaining the original logic. If you encounter any issues or need further adjustments, let me know!

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.