=sumproduct(round(
I am trying to figure out why sumproduct is suddenly returning #value!
At JE McGimpsey's suggestion, I have been using the following formula as a double check on a spread sheet =SUMPRODUCT(ROUND($F$63:$F$549*L63:L549,0)), it was working wonderfully this double-checks the sum of column M, which contains =ROUND((F108*L108),0) Column F & Column L have several text headers within the range. When I use Tools/Formula Auditing/Evaluate formula, the #value! error occurs at those text headers. The odd thing is, the spreadsheets I was using in Office XP had the exact same info, and didn't have an error when encountering text. I would just go back to the old spreadsheet, but it is corrupted. Excel help said to move all the old data into a new 2003 workbook. http://support.microsoft.com/kb/820712/en-us Now sumproduct is returning #value!. What am I doing wrong? |
=sumproduct(round(
Column F & Column L have several text headers within the range.
When I use Tools/Formula Auditing/Evaluate formula, the #value! error occurs at those text headers. As it should. text * 10 = #VALUE! 10 * text = #VALUE! text * text = #VALUE! Try this array formula** : =SUM(IF(ISNUMBER(F63:F549*L63:L549),ROUND(F63:F549 *L63:L549,0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "mhoffmeier" wrote in message ups.com... I am trying to figure out why sumproduct is suddenly returning #value! At JE McGimpsey's suggestion, I have been using the following formula as a double check on a spread sheet =SUMPRODUCT(ROUND($F$63:$F$549*L63:L549,0)), it was working wonderfully this double-checks the sum of column M, which contains =ROUND((F108*L108),0) Column F & Column L have several text headers within the range. When I use Tools/Formula Auditing/Evaluate formula, the #value! error occurs at those text headers. The odd thing is, the spreadsheets I was using in Office XP had the exact same info, and didn't have an error when encountering text. I would just go back to the old spreadsheet, but it is corrupted. Excel help said to move all the old data into a new 2003 workbook. http://support.microsoft.com/kb/820712/en-us Now sumproduct is returning #value!. What am I doing wrong? |
=sumproduct(round(
On Jul 31, 1:58 pm, "T. Valko" wrote:
Column F & Column L have several text headers within the range. When I use Tools/Formula Auditing/Evaluate formula, the #value! error occurs at those text headers. As it should. text * 10 = #VALUE! 10 * text = #VALUE! text * text = #VALUE! Try this array formula** : =SUM(IF(ISNUMBER(F63:F549*L63:L549),ROUND(F63:F549 *L63:L549,0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "mhoffmeier" wrote in message ups.com... I am trying to figure out why sumproduct is suddenly returning #value! At JE McGimpsey's suggestion, I have been using the following formula as a double check on a spread sheet =SUMPRODUCT(ROUND($F$63:$F$549*L63:L549,0)), it was working wonderfully this double-checks the sum of column M, which contains =ROUND((F108*L108),0) Column F & Column L have several text headers within the range. When I use Tools/Formula Auditing/Evaluate formula, the #value! error occurs at those text headers. The odd thing is, the spreadsheets I was using in Office XP had the exact same info, and didn't have an error when encountering text. I would just go back to the old spreadsheet, but it is corrupted. Excel help said to move all the old data into a new 2003 workbook. http://support.microsoft.com/kb/820712/en-us Now sumproduct is returning #value!. What am I doing wrong?- Hide quoted text - - Show quoted text - Wow, that works great. I'll have to study up on arrays. I still don't understand why the error did not appear back in Excel 2002 Thanks |
=sumproduct(round(
"mhoffmeier" wrote in message
ups.com... On Jul 31, 1:58 pm, "T. Valko" wrote: Column F & Column L have several text headers within the range. When I use Tools/Formula Auditing/Evaluate formula, the #value! error occurs at those text headers. As it should. text * 10 = #VALUE! 10 * text = #VALUE! text * text = #VALUE! Try this array formula** : =SUM(IF(ISNUMBER(F63:F549*L63:L549),ROUND(F63:F549 *L63:L549,0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "mhoffmeier" wrote in message ups.com... I am trying to figure out why sumproduct is suddenly returning #value! At JE McGimpsey's suggestion, I have been using the following formula as a double check on a spread sheet =SUMPRODUCT(ROUND($F$63:$F$549*L63:L549,0)), it was working wonderfully this double-checks the sum of column M, which contains =ROUND((F108*L108),0) Column F & Column L have several text headers within the range. When I use Tools/Formula Auditing/Evaluate formula, the #value! error occurs at those text headers. The odd thing is, the spreadsheets I was using in Office XP had the exact same info, and didn't have an error when encountering text. I would just go back to the old spreadsheet, but it is corrupted. Excel help said to move all the old data into a new 2003 workbook. http://support.microsoft.com/kb/820712/en-us Now sumproduct is returning #value!. What am I doing wrong?- Hide quoted text - - Show quoted text - Wow, that works great. I'll have to study up on arrays. I still don't understand why the error did not appear back in Excel 2002 Thanks Maybe what you were doing back in 2002 wasn't *exactly* the same thing you're doing now. Thanks for the feedback! -- Biff Microsoft Excel MVP |
All times are GMT +1. The time now is 01:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com