Combining "if" & "min" issues

[Tamarissa]

I'm trying to create a formula that will tell me what day of the week has the minimum price for a certain number of criteria, but can't seem to be able to figure it out.

This is my criteria that must be matched: AND([Hotel]=[@Hotel],[Departure Date]=[@[Departure Date]],[Week '#]=[@[Week '#]])

From here, I want it to find the minimum total per person value and return the "Day of Week Checked" value. I'm also hoping for it be able to return more than one day at a time, as sometimes the price is the same for 3-4 days of the week.

I've put in what I know the answer should be on a couple of cells in the "Minimum Price by Week" column because I may or may not have explained what I need well enough, and that might be clearer . One has just the first available criteria answer (Occidental Grand Xcaret), one has all the days with that price (Luxury Bahia Principe Sian Ka An).

Thanks!

[k64]

You cannot return more than one value to a cell without using macros. Do you want to do this with a macro or are you looking for formula-only answers?

[daffodil11]

Perhaps =MIN(IF((([Hotel]=[@Hotel])*([Departure Date]=[@[Departure Date]])*([Week '#]=[@[Week '#]]))>0,[Total / Person]))

Nesting other formulas with MIN usually requires the use of an array formula.

To enter this formula, hold Cltr+Alt and then hit Enter to exit the cell, instead of just hitting Enter.

[Tamarissa]

daffodil11: That didn't really work, sorry. It only retuned the minimum value contained in the entire sheet, not the day of the week for each hotel

k64: I'm not sure how a macro would work in this case, so I'd have to see how it worked... I just made the assumption that I would need a formula in the cell.

[FDibbins]

You cannot return more than one value to a cell without using macros. Do you want to do this with a macro or are you looking for formula-only answers?

Not really sure what you mean by this?

In the same cell, you can return a answer from several possible answers, or you can combine a number of different answers into 1 answer...

A1=date=6/6/2014
B1=Name=Bobby
C1=age=21
D1="In "year(A1)&", "B1&" will be "&C1&" years old"
In 2014, Bobby will be 21

[k64]

Yeah, you can concatenate things into a cell, but to my knowledge you can't do have one cell match a value to multiple other cells and return an array of responses. For example you can't do:
A1 = "M" B1="Bob"
A2 = "F" B2="Ann"
A3 = "M" B3="Joe"
A4 = Index(B1:B3,Match("M",A1:A3,All))= "Bob, Joe"

Tamarissa, I'd like to figure out how to do this properly with a macro, but don't have enough time this weekend. If you get your post resolved before then, I'll be interested in the answer. Otherwise, I'll take a look at it this coming week.

[Tamarissa]

Thank you k64, I would truly appreciate that

[k64]

Hi Tamarissa, I have some code, but it's not very good yet. I'll post it and see if I can do it another way. If any of the experts on this forum want to tell me why my code is running slowly and how to fix it, I welcome the advice.

Note two things about this current version:
1. You need to have Microsoft Active X Data Objects 6.1 Library added (check the box next to it under tools->references in the VBA menu)
2. This code is slow. I do NOT reccomend running it on larger data sets.

Like I said, I'll try a different avenue to see if I can make it faster.

[k64]

Hi again, here is my 2nd attempt. I thought this might be faster, but it doesn't help nearly as much as I hoped. The only real improvement in my opinion is that I added a "calculated" column, so that if you run it again, it won't recalculate for things that are marked "calculated". Note that since my code splits the data into groups that have the same date, hotel, and week, if any row in the group has not been calculated, the whole group will be recalculated. Hope it helps.

Other excel experts: Why is my code slow? The data-set is small (226 items) so I feel like my code should run instantaneously. I timed it though using a stopwatch (I probably should have just found some code for a timer online) and here are my results:
Using SQL: 13 seconds
SQL w/ screen-updating off: 12.3 seconds

Using autofilter: 15.8 seconds
Autofilter w/ screen updating off: 10.5 seconds
Autofilter w/ screen updating off and manual calculation: 7.25 seconds

I've been told that SQL and built in excel functions are faster than loops. How can this task be done faster?

[Tamarissa]

This is almost exactly what I'm looking for, thank you so much! As the file will get bigger, I like your idea of the calculated column so that it doesn't re-calculate everything

A couple of questions for you:

1. There are a lot of macros in the file... do I need to keep them all? As far as I can tell, I think the only one that's needed is "getminprice3" (I'm guessing the other two with the same names but earlier version numbers were the earlier phases; and I'm not sure if "Macro1-Macro6" & "test" are needed)
2. It looks like I need to manually run the macro every time I add data, is that correct?
3. If I could request one small change, I would really like to have the number that populates into the "Minimum Price by Week" column be the "Total / Person" value instead of the "Price / Person" value.

Thank you so much for this!

[k64]

1. You're welcome. I'm glad I could help getminprice and getminprice3 are the two working versions. They do it different ways, but all you'll notice as a difference is that getminprice uses the "Calculated" column and depending on your data, one or the other may be faster. No other macros are needed.
2. Yes, but you can make a button or keyboard shortcut to run it or you can set it to run everytime you change the sheet.
3. Here are two versions that use the Total / Person

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Tamarissa]

I've got it all sorted out, it's working beautifully, thanks so much!!

[k64]

Great! Glad I could help . Which version did you end up using? Did you make any modifications?

[Tamarissa]

I 'm using the "getminprice3()" version; the only thing I changed was the name. I like that one better since it only runs on the new data entered

[k64]

Cool. Let me know if you need anything else, and if you feel I've been helpful, feel free to give me +Rep.

[Tony Valko]

There's a good concatenation UDF here:

http://www.excelforum.com/tips-and-t...geravatar.html