Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Sometimes Working Sometimes Not
I have written a formula in excel:
=SUMPRODUCT(('Gross PAYM Talkplan Data'!$G$3:$G$3973=CH$119)*('Gross PAYM Talkplan Data'!$J$3:$J$3973=$A124&"P"&"18 month"),('Gross PAYM Talkplan Data'!$E$3:$E$3973)) In the data it is looking at, 1 of the criteria to pick up the correct numbers is either "P" or "SB" depending on what I'm looking at. In most instances, the above formula is bringing back only results with a P, but in some it's bringing back the results for both P & SB so my figures are wrong. The formula has been copied across & down in my table so there's no differences except for which column/row (CH$119/$A124) it looks at. Why is it doing this & how can I stop it? Thanks Miranda |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Sometimes Working Sometimes Not
I may be way off track, but it seems as if your problem is that you always
look at row 119 of Col CH, while you could be looking at any row in Col A. You say $A124, which means that if you copy down, it will become $A125. CH$119, when copied down, will however remain CH$119 "merry_fay" wrote: I have written a formula in excel: =SUMPRODUCT(('Gross PAYM Talkplan Data'!$G$3:$G$3973=CH$119)*('Gross PAYM Talkplan Data'!$J$3:$J$3973=$A124&"P"&"18 month"),('Gross PAYM Talkplan Data'!$E$3:$E$3973)) In the data it is looking at, 1 of the criteria to pick up the correct numbers is either "P" or "SB" depending on what I'm looking at. In most instances, the above formula is bringing back only results with a P, but in some it's bringing back the results for both P & SB so my figures are wrong. The formula has been copied across & down in my table so there's no differences except for which column/row (CH$119/$A124) it looks at. Why is it doing this & how can I stop it? Thanks Miranda |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Sometimes Working Sometimes Not
Hiya,
I'm afraid you are. It's a table with multiple columns & rows (136c x 112r) The formula looks for the contents of the cell on the same row in column A & the the contents of the the cell in the same column in row 119 (the CH changes E, F, G.... CH, CI, CJ etc) & the other 2 criteria. The problem doesn't relate to a particular column or row, it's just randomly happening as far as I can see. "kassie" wrote: I may be way off track, but it seems as if your problem is that you always look at row 119 of Col CH, while you could be looking at any row in Col A. You say $A124, which means that if you copy down, it will become $A125. CH$119, when copied down, will however remain CH$119 "merry_fay" wrote: I have written a formula in excel: =SUMPRODUCT(('Gross PAYM Talkplan Data'!$G$3:$G$3973=CH$119)*('Gross PAYM Talkplan Data'!$J$3:$J$3973=$A124&"P"&"18 month"),('Gross PAYM Talkplan Data'!$E$3:$E$3973)) In the data it is looking at, 1 of the criteria to pick up the correct numbers is either "P" or "SB" depending on what I'm looking at. In most instances, the above formula is bringing back only results with a P, but in some it's bringing back the results for both P & SB so my figures are wrong. The formula has been copied across & down in my table so there's no differences except for which column/row (CH$119/$A124) it looks at. Why is it doing this & how can I stop it? Thanks Miranda |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Sometimes Working Sometimes Not
Your formula looks ok to me.
I'd check to make sure that calculation was set to automatic (just in case). Then start looking at the data -- maybe you have hidden rows that you haven't noticed. If worse came to worse, you could test your results by putting a formula in an extra column and adding that up. merry_fay wrote: I have written a formula in excel: =SUMPRODUCT(('Gross PAYM Talkplan Data'!$G$3:$G$3973=CH$119)*('Gross PAYM Talkplan Data'!$J$3:$J$3973=$A124&"P"&"18 month"),('Gross PAYM Talkplan Data'!$E$3:$E$3973)) In the data it is looking at, 1 of the criteria to pick up the correct numbers is either "P" or "SB" depending on what I'm looking at. In most instances, the above formula is bringing back only results with a P, but in some it's bringing back the results for both P & SB so my figures are wrong. The formula has been copied across & down in my table so there's no differences except for which column/row (CH$119/$A124) it looks at. Why is it doing this & how can I stop it? Thanks Miranda -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Sometimes Working Sometimes Not
Hiya,
The calculation is set to manual (necessary or I sit & wait for 30+ mins each time I make a change) but I have definitely updated the cell. I've checked the source data too. There's definitely no hidden rows & when I filter it on the specifications in the formula, I find I should have a result of 27 for criteria "P". The result for criteria "SB" would be 1, I'm getting a result of 28. I have checked other cells with the formula in & in most cases it's working & only picking up the "P" cells. There's just a few cases where it's picking up both. As there's over 15k cells with this formula & it's only a small section of the overall spreadsheet, I am unfortunately unable to calculate everything manually. Thanks! "Dave Peterson" wrote: Your formula looks ok to me. I'd check to make sure that calculation was set to automatic (just in case). Then start looking at the data -- maybe you have hidden rows that you haven't noticed. If worse came to worse, you could test your results by putting a formula in an extra column and adding that up. merry_fay wrote: I have written a formula in excel: =SUMPRODUCT(('Gross PAYM Talkplan Data'!$G$3:$G$3973=CH$119)*('Gross PAYM Talkplan Data'!$J$3:$J$3973=$A124&"P"&"18 month"),('Gross PAYM Talkplan Data'!$E$3:$E$3973)) In the data it is looking at, 1 of the criteria to pick up the correct numbers is either "P" or "SB" depending on what I'm looking at. In most instances, the above formula is bringing back only results with a P, but in some it's bringing back the results for both P & SB so my figures are wrong. The formula has been copied across & down in my table so there's no differences except for which column/row (CH$119/$A124) it looks at. Why is it doing this & how can I stop it? Thanks Miranda -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Is it possible? | Excel Worksheet Functions | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Working Hours (formula & graph) - any elegant solution? | Excel Worksheet Functions | |||
Array Formula Not Working with Range with Formulas | Excel Discussion (Misc queries) |