Thread: IF/IFS Help
View Single Post
 
Old 11-22-2019, 10:35 AM
ortizimo ortizimo is offline Windows 7 64bit Office 2016
Novice
 
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