Hi so I'm basically a bit stuck and was wondering if anyone could be of help. I've built a dynamic workbook that is constantly changing and downloading data from another source online. Because of this there are often errors with #DIV/0 and #VALUE. It also needs to be filtered so am using a lot of Match and Index functions.
This is a general formula I've been using:
For certain variables the data source is pulling up (true) but negative numbers which so id like to replace the ((AGGREGATE(5,6,((INDEX($E:$E,MATCH("P/AFFO (FWD)",$A:$A,0))
(INDEX($R:$R,MATCH("P/AFFO (FWD)",$A:$A,0))))))))) with a formula that pulls the minimum positive number and ignore negative numbers from the E:R row corresponding to the correct variable but also ignores errors? basically if it was possible id add an aggregate if positive but this doesn't seem possible?
Any help would be appreciated and i know i haven't explained this very well and do apologise but hope this makes sense.
This is a general formula I've been using:
Excel Formula:
=(((INDEX($D$50:$D$99,MATCH("Variable 1",$A$50:$A$99,0))*((AGGREGATE(5,6,((INDEX($E:$E,MATCH("Variable 1",$A:$A,0)):((INDEX($R:$R,MATCH("Variable 1",$A:$A,0)))))))))/(((INDEX(E:E,MATCH("Variable 1",$A:$A,0))))))))For certain variables the data source is pulling up (true) but negative numbers which so id like to replace the ((AGGREGATE(5,6,((INDEX($E:$E,MATCH("P/AFFO (FWD)",$A:$A,0))
(INDEX($R:$R,MATCH("P/AFFO (FWD)",$A:$A,0))))))))) with a formula that pulls the minimum positive number and ignore negative numbers from the E:R row corresponding to the correct variable but also ignores errors? basically if it was possible id add an aggregate if positive but this doesn't seem possible?Any help would be appreciated and i know i haven't explained this very well and do apologise but hope this makes sense.
Last edited by a moderator:
