#1




IF/IFS Help
hi everyone. im in the middle of a project because im bored at work. i made a spreadsheet with 4 tables to enter computer parts, prices, s&h and rebates. then i have a 5th table that will get all the information from any of the other 4 using MIN.
I decided to make a NotAvail checkbox next to each item in case the item is not available. It will not affect the amount in the table as I still want it calculated but in the 5th table I want those cells with the checkbox = TRUE to be excluded then to look for the next available item (FALSE). I wrote this but it wont work and had different version I no longer remember. =IF(H5=TRUE,MIN(L21,D21,L5),IF(P5=TRUE,MIN(L21,D21 ,D5),IF(H21=TRUE,MIN(L21,L5,D5),IF(P21=TRUE,MIN(D2 1,L5,D5),MIN(D5,L5,D21,L21))))) I see the problem. If one cell = TRUE then MIN(the other 3), I repeat it on the second =TRUE but then I'm adding the cell that was previously removed as you can see from the formula. How can I continue MIN while excluding any of the other cells that = TRUE? Example: D5 = $109 L5 = $111 D21 = $114 L21 = $119 If D5 is checked then give me MIN from L5,D21,L21 If L5 is checked then give me MIN from D5,D21,L21 If D21 is checked then give me MIN from D5,L5,L21 if L21 is checked then give me MIN from D5,L5,D21 See the problem? I'm asking in to MIN cells I already asked not to in others IFs. Thanks. 
#2




Hi, ortizmo. I'm trying to get a biggerpicture idea of what's going on. A few questions:
1) You're trying to find the minimum price, I take it? 2) What's the significance of rows 5 and 21? Are the four tables all on the same worksheet, I'm guessing? 3) Isn't it possible that ~more~ than one table will have a part not available? Seems to me you're trying to make sure your formula works even when one value isn't available, but not considering that there may be more. 4) What is the value in the price column when the Unavailable box is checked? Does it stay the same as before? 5) There may a formula that'll work for this, but it seems complex enough that it may pay you to write your own worksheet function. And you did, after all, post this question in the VBA ~programming~ forum, so I suppose you've considered that option, even though you don't mention it? 
#3




#1/#2:
Ok, so let me explain...I have 5 tables in 1 spreadsheet. 4 of them hold prices for different online stores. The 5th one looks through all 4 and MIN is returned with formulas that change the item description based on the MIN found and also some comparison formulas to see the savings between them. The item I'm testing this on is a CPU price...the price for that item (which repeats in the other tables) are: D5, L5, D21, L21 respectively. My checkbox resides on the outside of each tables with a cell in FALSE which changes to TRUE when the check is ON. The cells for TRUE/FALSE are: H5, P5, H21, P21 respectively. And a MIN of all PRICE cells, if = FALSE. #3: On this question, you are correct...the thing is I'm testing the formula just for a single item (CPU). So yes, when D5 = TRUE then ignore it and continue with the others that are = FALSE (available). #4: The item still shows the price on its table, but the 5th table where this formula will reside will try to ignore it, hence, IF(D5=TRUE...) and not take the price into account. #5: I thought this area was for complicated formulas...not paying for programming...my apologies, that was not my intention. My formula causes trouble because if D5=True then MIN the rest...and the problem is what if I have others that are also not available?! Then I cannot write the next part because I already say ignore D5 go with the rest...but the third one is looking to the D5 included in the MIN which I already say ignore it...then it still shows up. I'm trying to find a way to take into account all possible combinations of not available into consideration. Not sure if I made the explanation even worse now. 
#4




I'd suggest you use something like this:
=MIN(IF(H5=TRUE,1E100,D5),IF(P5=TRUE,1E100,L5),IF( H21=TRUE,1E100,D1),IF(P21=TRUE,1E100,L21)) which will return 1E100 (a very big number) for any cells where the checkbox is TRUE. If the end result is 1E100, then none of the items are available, which you could add an additional test for if it could happen? 
#5




Jumping straight to #5: LOL, no, not "paying" for programming. We help each other on this forum (and on most others that I know of) because we love it, not for pay. In this forum, the ExcelProgramming forum I mean, we talk about programs that you can write ~yourself~ to accomplish this, in the VBA language ("Visual Basic for Applications", a flavor of Basic that Microsoft includes builtin with all its Office apps including Excel). You can do things much more complex once you break down and try it, and for my money once you're used to it there are things that are actually easier to do in VBA than using a complex Excel formula. But it's up to you, of course.
If you resolutely avoid writing something in VBA then it's not clear to me how you'd do this with a single formula. Debaser seems to be on the right track when he suggests one formula for checking the checkboxes, with another formula (or more than one more) for other purposes. I'm thinking you could do it with an array of formulae (so to speak). If you write your own worksheet formula, though, you could give it a name that makes sense to you — ORTIZMO, maybe — and then ORTIZMO, just like MIN, IF, VLOOKUP etc, could be used in a (much simpler) formula, because it did all the checking that you're having trouble stuffing into a single formula otherwise. But if you don't already know anything about Basic, it would, I admit, learning a new skill. We'd help, of course. I would, at least. 
#6




Quote:
That worked as I wanted...thanks very much! Thanks everyone! 
Thread Tools  
Display Modes  
