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.
|