I have this formula in my model:…
- User
I have this formula in my model: =IF(113="Equally",(I65+I68+I71+I74+I77+I80+I83+I86+I89+I92+I95+I98+I101+I104+I107+I110)/16,IF(113="Confidence",(I65I66+I68I69+I71I72+I74I75+I77I78+I80I81+I83I84+I86I87+I89I90+I92I93+I95I96+I98I99+I101I102+I104I105+I107I108+I110I111)/16,IF(113="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(66:111,"&",REPT(" ",100)),100))),0))),IF(113="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(66:111,"&",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?
- 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 usingIF(ISNUMBER(), ,0)to check if a cell contains a number and return 0 if it does not.Additionally, I've used
SUMPRODUCTwith 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:
SUMPRODUCTis 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 includesIFERRORto 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.
- The weight extraction (e.g.,
-
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
SUMPRODUCTwith 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!
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.