Microsoft Office Forums IF/IFS Help
 Register FAQ Search Today's Posts Mark Forums Read

#1
11-22-2019, 10:35 AM
 ortizimo Windows 7 64bit Office 2016 Novice Join Date: Nov 2019 Posts: 3
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
11-23-2019, 10:30 AM
 BobBridges Windows 7 64bit Office 2010 32bit Expert Join Date: May 2013 Location: USA Posts: 621

Hi, ortizmo. I'm trying to get a bigger-picture 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
11-27-2019, 09:49 AM
 ortizimo Windows 7 64bit Office 2016 Novice Join Date: Nov 2019 Posts: 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
11-28-2019, 07:31 AM
 Debaser Windows 7 64bit Office 2010 32bit Competent Performer Join Date: Oct 2015 Posts: 170

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
11-28-2019, 09:57 AM
 BobBridges Windows 7 64bit Office 2010 32bit Expert Join Date: May 2013 Location: USA Posts: 621

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 Excel-Programming 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 built-in 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
12-02-2019, 11:40 AM
 ortizimo Windows 7 64bit Office 2016 Novice Join Date: Nov 2019 Posts: 3

Quote:
 Originally Posted by Debaser 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?

That worked as I wanted...thanks very much! Thanks everyone!

 Thread Tools Display Modes Linear Mode

All times are GMT -7. The time now is 02:02 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top