Microsoft Office Forums IF/IFS Help

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-22-2019, 10:35 AM
ortizimo ortizimo is offline IF/IFS Help Windows 7 64bit IF/IFS Help Office 2016
Novice
IF/IFS Help
 
Join Date: Nov 2019
Posts: 3
ortizimo is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 11-23-2019, 10:30 AM
BobBridges's Avatar
BobBridges BobBridges is offline IF/IFS Help Windows 7 64bit IF/IFS Help Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 621
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

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?
Reply With Quote
  #3  
Old 11-27-2019, 09:49 AM
ortizimo ortizimo is offline IF/IFS Help Windows 7 64bit IF/IFS Help Office 2016
Novice
IF/IFS Help
 
Join Date: Nov 2019
Posts: 3
ortizimo is on a distinguished road
Default

#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.
Reply With Quote
  #4  
Old 11-28-2019, 07:31 AM
Debaser's Avatar
Debaser Debaser is offline IF/IFS Help Windows 7 64bit IF/IFS Help Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 170
Debaser is on a distinguished road
Default

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?
Reply With Quote
  #5  
Old 11-28-2019, 09:57 AM
BobBridges's Avatar
BobBridges BobBridges is offline IF/IFS Help Windows 7 64bit IF/IFS Help Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 621
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

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.
Reply With Quote
  #6  
Old 12-02-2019, 11:40 AM
ortizimo ortizimo is offline IF/IFS Help Windows 7 64bit IF/IFS Help Office 2016
Novice
IF/IFS Help
 
Join Date: Nov 2019
Posts: 3
ortizimo is on a distinguished road
Default

Quote:
Originally Posted by Debaser View Post
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!
Reply With Quote
Reply

Thread Tools
Display Modes



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


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft