Nested IF greater than and less than

[JimmyJam]

Hello, everyone. I did not find an answer to this in previously posted questions, so I'll ask...

Background: I have a product pricing calculator where I need to summarize ten years of licensing fees with yearly price escalators. On one tab, I have a pricing matrix with four rows of quantity tiers and ten columns of year over year pricing. I also have two cells for each price tier that contains the low and high limits of the tier because these can change.

Solution needed: On the main tab, I need non-pricing people to be able to enter a number of devices and get a single row of ten prices based on where their quantity falls in the tier structure. This is only good for one tier. =IF(AND(B3>ISE!C3,B3<ISE!D3),E9,ISE!E3) where ISE is the tab with the pricing matrix. How do I essentially "OR" this formula so that if it is not true, it evaluates other tiers?

I'm not an Excel pro, so I apologize if I am making this confusing.

Thanks in advance for any help!

 

[JimmyJam]

Actually, I guess that formula does not work. Returns row from other tab regardless of value of B3

 

[Joe4]

Welcome to the Board!

Often times, these questions make a lot more sense to us when you can see what the data you are working with looks like, and what your expected results should look like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[JimmyJam]

Welcome to the Board!

Often times, these questions make a lot more sense to us when you can see what the data you are working with looks like, and what your expected results should look like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thank you, Joe4. That is very cool tool. What I have now is kind of a mess and has confidential information sprinkled about in it. I've also been asked to simply provide a tab per price tier, so that just negated the need for now.

I've used this forum as a lurker for years. Glad to be "official", now!

 

[Joe4]

OK, I am glad your issue is solved, for now anyway.
If you ever do have a need in the future, just post some "dummied up" data to remove the sensitive stuff, but shows us your structure and what you are after.