| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Bar F9 Result differs from cell result???
This is weird. This formula returns a zero in the cell:
=SUMPRODUCT((IF($C$5<"*",'Trial Balance'!$I$2:$I$65536,RIGHT('Trial Balance'!$I$2:$I$65536,8))=IF($C$5<"*",$C$5&"S636 4002","S6364002"))*('Trial Balance'!$J$2:$J$65536=U$6)*('Trial Balance'!$E$2:$E$65536)) But if I highlight the formula in the formula bar and hit F9 it returns 106934.03 106934.03 is the correct answer! Why would I be getting a zero in the cell??? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Bar F9 Result differs from cell result???
Enter the formula as an array.
Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Aaron" wrote in message ... This is weird. This formula returns a zero in the cell: =SUMPRODUCT((IF($C$5<"*",'Trial Balance'!$I$2:$I$65536,RIGHT('Trial Balance'!$I$2:$I$65536,8))=IF($C$5<"*",$C$5&"S636 4002","S6364002"))*('Trial Balance'!$J$2:$J$65536=U$6)*('Trial Balance'!$E$2:$E$65536)) But if I highlight the formula in the formula bar and hit F9 it returns 106934.03 106934.03 is the correct answer! Why would I be getting a zero in the cell??? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Bar F9 Result differs from cell result???
Thank you, I will. I use variations of Sumproduct all the time and never had
to before. Is it the nested Right() function that is casusing the need to use an array? "T. Valko" wrote: Enter the formula as an array. Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Aaron" wrote in message ... This is weird. This formula returns a zero in the cell: =SUMPRODUCT((IF($C$5<"*",'Trial Balance'!$I$2:$I$65536,RIGHT('Trial Balance'!$I$2:$I$65536,8))=IF($C$5<"*",$C$5&"S636 4002","S6364002"))*('Trial Balance'!$J$2:$J$65536=U$6)*('Trial Balance'!$E$2:$E$65536)) But if I highlight the formula in the formula bar and hit F9 it returns 106934.03 106934.03 is the correct answer! Why would I be getting a zero in the cell??? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Bar F9 Result differs from cell result???
Is it the nested Right() function that is casusing the
need to use an array? Yes, when combined within the IF function. -- Biff Microsoft Excel MVP "Aaron" wrote in message ... Thank you, I will. I use variations of Sumproduct all the time and never had to before. Is it the nested Right() function that is casusing the need to use an array? "T. Valko" wrote: Enter the formula as an array. Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Aaron" wrote in message ... This is weird. This formula returns a zero in the cell: =SUMPRODUCT((IF($C$5<"*",'Trial Balance'!$I$2:$I$65536,RIGHT('Trial Balance'!$I$2:$I$65536,8))=IF($C$5<"*",$C$5&"S636 4002","S6364002"))*('Trial Balance'!$J$2:$J$65536=U$6)*('Trial Balance'!$E$2:$E$65536)) But if I highlight the formula in the formula bar and hit F9 it returns 106934.03 106934.03 is the correct answer! Why would I be getting a zero in the cell??? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Result of a formula in another cell | Excel Worksheet Functions | |||
| Formula result in different cell | Excel Worksheet Functions | |||
| vlookup shows result one cell above the expected result | Excel Worksheet Functions | |||
| Advanced formula - Return result & Show Cell Reference of result | Excel Worksheet Functions | |||
| Median result used in formula gives incorrect result | Excel Worksheet Functions | |||
Linear Mode
