Log In
Register
Home
April 6, 2018
Offline
Hello - I've attached a model of what I'm needing, I'm just not quite sure how to write the formula.
Here's the logic for the formula - (IF P10 is within range in colums E and G multiply corresponding number in colum K times Q10 plus D4)
Any help would be much appreciated.
Thanks, Ryan
See Answer
VIP
April 21, 2015
Offline
Please check your post after posting. There is no attachment!
April 6, 2018
Offline
Ok thanks... Try this.
VIP
Trusted Members
June 25, 2016
Offline
Hi Ryan
Give this a try.
Sunny
April 6, 2018
Offline
Thank you Sunny! I am not able to open the file. Any suggestions?
Thanks, Ryan
VIP
Trusted Members
December 7, 2016
Offline
Hello Ryan,
Just wondering if the tip in this VLOOKUP article can give you some way forward in creating such calculator.
Br,
Anders
Answers Post
VIP
Trusted Members
June 25, 2016
Offline
Hi Ryan
I am re-attaching the file again.
Please try.
Sunny
VIP
April 21, 2015
Offline
Sunny, both files opened here perfectly in Excel 2013.
Frans
April 6, 2018
Offline
Thank you all for your feedback. I was able to open the attachment and the VLOOKUP article is very helpful.
Regards, Ryan
April 6, 2018
Offline
Well just when I thought I had it... Conditions drastically changed so what I thought I needed is now more complex then I first realized.
I've attached the file and basically the concept of the calculator has not changed but the array area is much larger and more complex.
I looked at each of the VLOOKUP articles that Anders shared but I am not able to make it work.
Any help would be greatly appreciated.
Ryan
VIP
Trusted Members
December 7, 2016
Offline
Hello Ryan,
Yes, when you get a matrix like the example you share, then you are better off using INDEX and MATCH.
Based on your file, the formula in cell J27 would be like below. I have also attached your sample file with the change.
=INDEX($E$6:$Y$23;MATCH($H$27;$B$6:$B$23;1);MATCH($I$27;$E$5:$Y$5;0))
What this formula does is following:
Within cells E6:Y23, find the intersection of the value in cell H27 and cell I27 and show the value in that cell.
To find correct row and column we use MATCH function.
More information about INDEX and MATCH you can find in the blog section.
You might be confused that my formula has semicolon (;) as divider in the functions and not comma (,). That is because of the regional settings. When you open the attached file it will show correct based on your settings.
Good luck with this calculator!
Br,
Anders
April 6, 2018
Offline
Got it. This is great Anders! I really appreciate your help.
Is there anything I can do to help support this site? The resources provided through this site are amazing!
Ryan
VIP
April 21, 2015
Offline
Ryan wrote: "Is there anything I can do to help support this site? The resources provided through this site are amazing!"
Answer: Spread the word around! 🙂
Frans
VIP
Trusted Members
December 7, 2016
Offline
Yep, I can only agree. The articles in this blog was what caught my attention first, I have learned a lot from them. Then I took some courses and they were great, especially the training materials that are included. Also the forum here is great, the chances to learn a trick or two are good.
I try to participate in this forum as whilst I can help others I also get to learn new things myself.
/Anders
1 Guest(s)
