 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2008Nested if function
I want to return the minimum value of about 13 cells in a column (column B). I only want to include values where the text in column K is TRUE (based on a different IF formula from another cell). TIA Something like: =if(K1="TRUE",min(b1:b13),"what you want displayed when K is FALSE") Regards, Fred. >I want to return the minimum value of about 13 cells in a column (column >B). I only want to include values where the text in column K is TRUE (based >on a different IF formula from another cell). > TIA I need the formula to look for "TRUE" in EACH row to determine whether or not to use the figure in column B. This formula did not work--=IF(K7:K18="TRUE",MAX(B7,B8,B9,B10,B11,B14,B15,B16,B17,B18,B19,B20)). The cell returns #VALUE! when I try this formula. Note that some of the cells K7 to K18 will contain the formula result "TRUE" and some "FALSE." > Something like: > [quoted text clipped - 7 lines] >>(based on a different IF formula from another cell). >> TIA What do you want to happen when a cell is TRUE, and what when it's FALSE? Do you, for example, want to find the Max of only the TRUEs? Or something else? Fred >I need the formula to look for "TRUE" in EACH row to determine whether or >not to use the figure in column B. This formula did not [quoted text clipped - 12 lines] >>>(based on a different IF formula from another cell). >>> TIA Let me explain in words what I am trying to do since I am not communicating well with functions. I have several employees that run production routes. Their production totals are in column B. I only want to compare the production for employees certain types of equipment. They use different equipment on different days. I haven't discussed it, but I use an OR formula to give me a true or false (based on whether they are using the equipment whose production I want included in the MIN or MAX comparisons). The OR formulas returning TRUE or FALSE are in column K. I just want to find the MIN for the employees that are using certain equipment (in other words that the OR formula returns TRUE for in column K). Thank you for your patience. > What do you want to happen when a cell is TRUE, and what when it's FALSE? > Do you, for example, want to find the Max of only the TRUEs? Or something [quoted text clipped - 18 lines] >>>>(based on a different IF formula from another cell). >>>> TIA What's wrong with the solution we gave you? Did it not work? Did you get an error message? We can't help you much if you don't tell us what happened. Regards, Fred. > Let me explain in words what I am trying to do since I am not > communicating well with functions. I have several employees that run [quoted text clipped - 30 lines] >>>>>TRUE (based on a different IF formula from another cell). >>>>> TIA The formula returns #VALUE! > What's wrong with the solution we gave you? Did it not work? Did you get > an error message? We can't help you much if you don't tell us what [quoted text clipped - 37 lines] >>>>>>is TRUE (based on a different IF formula from another cell). >>>>>> TIA Well at least now we are getting somewhere. Now we need to know: Whose formula did you use, mine or Ron's? Showing us the exact formula you used will help. What's in the data range? Is it all numbers, or is there text or blanks in some cells? Regards, Fred. > The formula returns #VALUE! >> What's wrong with the solution we gave you? Did it not work? Did you get [quoted text clipped - 39 lines] >>>>>>>is TRUE (based on a different IF formula from another cell). >>>>>>> TIA =IF(K7:K18="TRUE",MAX(B7,B8,B9,B10,B11,B14,B15,B16,B17,B18,B19,B20)). >The cell returns #VALUE! when I try this formula. There are some blanks but >other than blanks the cells in column K contain the result of the OR [quoted text clipped - 54 lines] >>>>>>>>is TRUE (based on a different IF formula from another cell). >>>>>>>> TIA More progress. Now go back and read the answers you were given. You will find your problem. Regards, Fred. > =IF(K7:K18="TRUE",MAX(B7,B8,B9,B10,B11,B14,B15,B16,B17,B18,B19,B20)). >>The cell returns #VALUE! when I try this formula. There are some blanks [quoted text clipped - 56 lines] >>>>>>>>>K is TRUE (based on a different IF formula from another cell). >>>>>>>>> TIA I give up-thank you for trying to help. > More progress. Now go back and read the answers you were given. You will > find your problem. [quoted text clipped - 62 lines] >>>>>>>>>>K is TRUE (based on a different IF formula from another cell). >>>>>>>>>> TIA Sorry to hear that. The solution was so simple. But if you are not interested in following our advice, I guess there's nothing we can do. Regards, Fred. >I give up-thank you for trying to help. >> More progress. Now go back and read the answers you were given. You will [quoted text clipped - 64 lines] >>>>>>>>>>>cell). >>>>>>>>>>> TIA After rereading your initial post, I think the following will do what you want. First, you need an array formula, which you create by using Control-Shift-Enter, rather than just Enter. Second, do you want the minimum value, or the maximum value? You say Min in your initial post, but used Max in the formula. Why the discrepancy? Finally, your ranges have to be the same size. If you're testing K7:K18, then your values range must be B7:B18, not B7:B20. Assuming you want the minimum, try the following: =MIN(IF(K7:K18="TRUE",B7:B18,0)) Remember to commit with Ctrl-Shift-Enter. Regards, Fred. >I need the formula to look for "TRUE" in EACH row to determine whether or >not to use the figure in column B. This formula did not [quoted text clipped - 12 lines] >>>(based on a different IF formula from another cell). >>> TIA >I need the formula to look for "TRUE" in EACH row to determine whether or >not to use the figure in column B. This formula did not >work--=IF(K7:K18="TRUE",MAX(B7,B8,B9,B10,B11,B14,B15,B16,B17,B18,B19,B20)). >The cell returns #VALUE! when I try this formula. Note that some of the >cells K7 to K18 will contain the formula result "TRUE" and some "FALSE." That formula is incorrect. Try this: =MAX(IF(K7:K18=TRUE,B7:B18)) NOTE: This formula is an **ARRAY** formula. After you type or paste it into your cell, enter it by holding down <ctrl><shift> while you hit <enter>. If you do this correctly, Excel will place braces {...} around the formula in the cell --ron |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.