hi,
I am having real problems with this as my range changed to columns H:BJ
H is where the products are
BJ is where the average is
What parts of the forumla should I amend? (Sorry)
You just need to change all the $S to $BJ you can do it with Edit|Replace...
=IFERROR(IF(INDEX(Overview!$H$5:$BJ$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH($G$4,Overview!$H$3:$BJ$3,0))<1,INDEX(Overview!$H$5:$BJ$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH("Average",Overview!$H$4:$BJ$4,0)),INDEX(Overview!$H$5:$BJ$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH($G$4,Overview!$H$3:$BJ$3,0))),0)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Hi,
I have had to upload the sheet to show you.
The average column isnt being picked up correctly. What is it?!?
What is wrong... Column BJ at row 5 (where the match occurs) is 35.77 as per the result in Q23?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Sorry - that was me being stupid i think!! I just have to test it - ill report back the outcome asap
Hi,
This formula works fine for me:
however - I have sent it to someone else who is not on 2007 and it doesnt work because of the IFERROR function=IFERROR(IF(INDEX(Overview!$H$5:$BJ$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH($G$4,Overview!$H$3:$BJ$3,0))<1,INDEX(Overview!$H$5:$BJ$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH("Average",Overview!$H$4:$BJ$4,0)),INDEX(Overview!$H$5:$BJ$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH($G$4,Overview!$H$3:$BJ$3,0))),0)
Can anyone help in changing it to a formula that will work on older versions?
You might just manage this in 2003
i.e.=IF(ISERROR(IF(INDEX(Overview!$H$5:$BJ$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH($G$4,Overview!$H$3:$BJ$3,0))<1,INDEX(Overview!$H$5:$BJ$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH("Average",Overview!$H$4:$BJ$4,0)),INDEX(Overview!$H$5:$BJ$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH($G$4,Overview!$H$3:$BJ$3,0)))),0,IF(INDEX(Overview!$H$5:$BJ$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH($G$4,Overview!$H$3:$BJ$3,0))<1,INDEX(Overview!$H$5:$BJ$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH("Average",Overview!$H$4:$BJ$4,0)),INDEX(Overview!$H$5:$BJ$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH($G$4,Overview!$H$3:$BJ$3,0))))
=IF(ISERROR("your formula"),"","your formula")
rather than
=IFERROR("your formula","")
If you need any more information, please feel free to ask.
However, if this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the small Star icon botom left of thier post to show your appreciation.
Try:
=IF(ISNA(INDEX(Overview!$H$5:$BJ$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH($G$4,Overview!$H$3:$BJ$3,0))),0,IF(INDEX(Overview!$H$5:$BJ$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH($G$4,Overview!$H$3:$BJ$3,0))<1,INDEX(Overview!$H$5:$BJ$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH("Average",Overview!$H$4:$BJ$4,0)),INDEX(Overview!$H$5:$BJ$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH($G$4,Overview!$H$3:$BJ$3,0))))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thanks Marcol - it would be so much easier if everyone I worked with was on the same version of excel!
Thanks NBVC, if the otherone doesnt work Ill try yours. I am waiting to here from my colleague.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks