Hi there,
I have used an advanced filter to filter down my dataset to football matches involving Arsenal and I'm now trying to add an IF formula. My formula (which is in M36) is
=IF(AND(B23="Arsenal",C36="Arsenal"),N23,IF(AND(C23="Arsenal",C36="Arsenal"),O23,""))
It's basically stating that if Arsenal are playing away in this game and were at home in their last game, take the value for HomeShots in the last game (N23), however if Arsenal are away in this game and were away in the previous game then take the value of AwayShots in last game (O23). The formula works perfectly, however when I try to drag it down through to the other records it seems to take cell references from outside the filtered range. Is there any way around this? Unfortunately I'm not familiar with building Macros and manual entry is not really an option as I have a dataset of thousands of games; this will need to be replicated with different teams and different variables/
Thanks in advance for your help.
Dan
I have used an advanced filter to filter down my dataset to football matches involving Arsenal and I'm now trying to add an IF formula. My formula (which is in M36) is
=IF(AND(B23="Arsenal",C36="Arsenal"),N23,IF(AND(C23="Arsenal",C36="Arsenal"),O23,""))
It's basically stating that if Arsenal are playing away in this game and were at home in their last game, take the value for HomeShots in the last game (N23), however if Arsenal are away in this game and were away in the previous game then take the value of AwayShots in last game (O23). The formula works perfectly, however when I try to drag it down through to the other records it seems to take cell references from outside the filtered range. Is there any way around this? Unfortunately I'm not familiar with building Macros and manual entry is not really an option as I have a dataset of thousands of games; this will need to be replicated with different teams and different variables/
Thanks in advance for your help.
Dan
