Seeking guidance to find max and min from a table

**aussiemum32** · 07-07-2012, 10:05 PM

Excel max min formula Capture.jpg
Hi there i know that my capture may not be that big, because I had so much information to capture. My problem is that I need a formula to fill Accomadation MAx and Accomadation Min from the table below showing Hotel, Standard Rate, Discount Rate, and in column G is where each person is either a Standard Rate or Discount Rate for the hotel. What I need is a formula that will allow each person according to either Standard or Discount from the table below to each have a max and min amount so those who are standard will have a max and min amount from the table column labelled standard Rate and those with a Discount rate in their column will have a max and min rate from the table column labelled discount rate. I hope thats not too confusing. So as an example "Mary Mason", will have a discount hotel rate, so using the discount rate column in the table below, the max rate will be $103.50 and min will be $71.25. Only I can't figure out a formula that I can use to incorporate the table information for each person according to "standard" and "discount" rate, and a formula for each column "accomodation max" and "accomodation min" that autofills down for each person. I have made it this far on my own with figuring out the other formulas, but this one is tough. So any help will be greatly appreciated. Thanks to anyone who can help me.

**jeffreybrown** · 07-07-2012, 10:09 PM

You are right, that picture is small and at least for me hard to read. Since the picture is your worksheet, can you attach the worksheet instead as long as there isn't any confidential information?

**aussiemum32** · 07-08-2012, 12:24 AM

Hope that helps give you a better idea of what I am trying to do with this. It's for an assessment tafe course in certificate III, and I only started learning about excel formulas a few months ago.

**jeffreybrown** · 07-08-2012, 08:33 AM

Try

In M4 copied down

Formula:

Please Login or Register  to view this content.

or

Formula:

Please Login or Register  to view this content.

In N4 copied down

Formula:

Please Login or Register  to view this content.

or

Formula:

Please Login or Register  to view this content.

**aussiemum32** · 07-08-2012, 11:11 AM

thanks so much they worked and you absolutely saved me hours of work, and better still I understood them so know I know how to use them should i need them again in the future, I thought one formula was hard enough but these ones using IF, MIN, Max and all the rest, so confusing. I only hope someday they will all make sense. Thanks again, now I am one step closer to finishing my assessment and moving onto the next one. I am trying to complete a certificate III in information technology and about 3 quarters of the way through.

**jeffreybrown** · 07-08-2012, 05:40 PM

With due diligence they will surly make sense in time

You are most welcome. Glad it worked for you and thanks for the feedback

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save