Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula bar deception
I have a nested formula that I am trying to enter into a cell. When I enter
it into the formula bar, the 'Formula Result' is as I expect but when I hit 'OK', the number displaying in the cell is different and apparently random. I've tried recalculating, retyping, simplifying (nested IF functions), all to no avail. The formula bar always looks right and the cell displays wrong. (It's not a rounding error, I'm trying to get a specific single-digit number and I get a different single-digit number.) Help me please! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula bar deception
....and your formula is....what?
*********** Regards, Ron XL2002, WinXP "Stephanie" wrote: I have a nested formula that I am trying to enter into a cell. When I enter it into the formula bar, the 'Formula Result' is as I expect but when I hit 'OK', the number displaying in the cell is different and apparently random. I've tried recalculating, retyping, simplifying (nested IF functions), all to no avail. The formula bar always looks right and the cell displays wrong. (It's not a rounding error, I'm trying to get a specific single-digit number and I get a different single-digit number.) Help me please! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula bar deception
Hi Stephanie
Post your formula, and what the contents of the cells being compared with your If statements, then maybe we can help you. -- Regards Roger Govier "Stephanie" wrote in message ... I have a nested formula that I am trying to enter into a cell. When I enter it into the formula bar, the 'Formula Result' is as I expect but when I hit 'OK', the number displaying in the cell is different and apparently random. I've tried recalculating, retyping, simplifying (nested IF functions), all to no avail. The formula bar always looks right and the cell displays wrong. (It's not a rounding error, I'm trying to get a specific single-digit number and I get a different single-digit number.) Help me please! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula bar deception
=SUM(IF('Personnel-Summary'!$D$6:$D$101='Personnel-Breakdown'!$A8,(IF('Personnel-Summary'!$E$6:$E$101='Personnel-Breakdown'!C$7,1,0)),0))
I am trying to match office location and title from the Personnel-Summary to fields in the Personnel-Breakdown tab and count the number of matches. As I said earlier, I get the correct total in the formula bar but a different number appears when I click 'OK'. "Stephanie" wrote: I have a nested formula that I am trying to enter into a cell. When I enter it into the formula bar, the 'Formula Result' is as I expect but when I hit 'OK', the number displaying in the cell is different and apparently random. I've tried recalculating, retyping, simplifying (nested IF functions), all to no avail. The formula bar always looks right and the cell displays wrong. (It's not a rounding error, I'm trying to get a specific single-digit number and I get a different single-digit number.) Help me please! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula bar deception
Regarding:
=SUM(IF('Personnel-Summary'!$D$6:$D$101='Personnel-Breakdown'!$A8,(IF('Personnel-Summary'!$E$6:$E$101='Personnel-Breakdown'!C$7,1,0)),0)) I believe that is an ARRAY FORMULA. Consequently, you need to hold down the [ctrl] and [shift] keys when you press [enter], instead of just pressing [enter]. Alternatively, you might want to consider a SUMPRODUCT, non-array approach: =SUMPRODUCT(('Personnel-Summary'!$D$6:$D$101='Personnel-Breakdown'!$A8)*('Personnel-Summary'!$E$6:$E$101='Personnel-Breakdown'!C$7)) (Just press [enter] for that one) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Stephanie" wrote: =SUM(IF('Personnel-Summary'!$D$6:$D$101='Personnel-Breakdown'!$A8,(IF('Personnel-Summary'!$E$6:$E$101='Personnel-Breakdown'!C$7,1,0)),0)) I am trying to match office location and title from the Personnel-Summary to fields in the Personnel-Breakdown tab and count the number of matches. As I said earlier, I get the correct total in the formula bar but a different number appears when I click 'OK'. "Stephanie" wrote: I have a nested formula that I am trying to enter into a cell. When I enter it into the formula bar, the 'Formula Result' is as I expect but when I hit 'OK', the number displaying in the cell is different and apparently random. I've tried recalculating, retyping, simplifying (nested IF functions), all to no avail. The formula bar always looks right and the cell displays wrong. (It's not a rounding error, I'm trying to get a specific single-digit number and I get a different single-digit number.) Help me please! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula bar deception
YOU ARE AWESOME!
I completely forgot about the array feature. Holding the extra keys did the trick. Also, I've never used the SUMPRODUCT function, so I'll have to check that one out. Thanks so much for your time! -Stephanie "Ron Coderre" wrote: Regarding: =SUM(IF('Personnel-Summary'!$D$6:$D$101='Personnel-Breakdown'!$A8,(IF('Personnel-Summary'!$E$6:$E$101='Personnel-Breakdown'!C$7,1,0)),0)) I believe that is an ARRAY FORMULA. Consequently, you need to hold down the [ctrl] and [shift] keys when you press [enter], instead of just pressing [enter]. Alternatively, you might want to consider a SUMPRODUCT, non-array approach: =SUMPRODUCT(('Personnel-Summary'!$D$6:$D$101='Personnel-Breakdown'!$A8)*('Personnel-Summary'!$E$6:$E$101='Personnel-Breakdown'!C$7)) (Just press [enter] for that one) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Stephanie" wrote: =SUM(IF('Personnel-Summary'!$D$6:$D$101='Personnel-Breakdown'!$A8,(IF('Personnel-Summary'!$E$6:$E$101='Personnel-Breakdown'!C$7,1,0)),0)) I am trying to match office location and title from the Personnel-Summary to fields in the Personnel-Breakdown tab and count the number of matches. As I said earlier, I get the correct total in the formula bar but a different number appears when I click 'OK'. "Stephanie" wrote: I have a nested formula that I am trying to enter into a cell. When I enter it into the formula bar, the 'Formula Result' is as I expect but when I hit 'OK', the number displaying in the cell is different and apparently random. I've tried recalculating, retyping, simplifying (nested IF functions), all to no avail. The formula bar always looks right and the cell displays wrong. (It's not a rounding error, I'm trying to get a specific single-digit number and I get a different single-digit number.) Help me please! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |