Formulas that compare data in other cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2708709%22%20slang%3D%22en-US%22%3EFormulas%20that%20compare%20data%20in%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2708709%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20write%20a%20formula%20for%20a%20cell%20that%20will%20compare%20data%20from%20other%20cells%20resulting%20in%20the%20lowest%20amount%20in%20the%20target%20cell.%20Here's%20what%20I%20need%3A%20if%20F24%20is%20less%20than%20C14%2C%20J14%20and%20E18%20then%20the%20result%20should%20be%20F24.%20If%20any%20of%20the%20values%20are%20lower%20than%20F24%20then%20the%20value%20in%20that%20cell%20should%20be%20the%20result.%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20what%20I%20have%3A%3C%2FP%3E%3CP%3E%3DIF(F24%3CC14%3EC14%2CC14%2CIF(F24%3CJ14%3EJ14%2CJ14%2CIF(F24%3CE18%3EE18%2CE18))))))%3C%2FE18%3E%3C%2FJ14%3E%3C%2FC14%3E%3C%2FP%3E%3CP%3EIt%20seems%20to%20work%20for%20the%20comparison%20between%20F24%20and%20C14%20but%20not%20for%20the%20other%20fields.%26nbsp%3B%20I%20should%20also%20mention%20that%20each%20of%20these%20fields%20have%20basic%20math%20calculations%20in%20them%20and%20some%20are%20dependent%20on%20drop%20downs%20(not%20sure%20if%20any%20of%20that%20should%20make%20a%20difference).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2708709%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2708882%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20that%20compare%20data%20in%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2708882%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1143799%22%20target%3D%22_blank%22%3E%40gpecoraro%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DMIN(F24%2CC14%2CJ14%2CE18)%3C%2FSTRONG%3E%20is%20a%20formula%20that%20will%20deliver%20the%20lowest%20of%20the%20four%20cells%20referenced.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20took%20quite%20a%20bit%20of%20hunting%20to%20find%20the%20cell%20in%20which%20your%20formula%20resided.%20If%20I%20may%20suggest%2C%20if%20you%20come%20back%20looking%20for%20help%2C%20it%20helps%20others%20help%20you%20if%20the%20spreadsheet%20you%20attach--thank%20you%20for%20doing%20that--somehow%20highlights%20where%20the%20difficulty%20resides%20AND%20if%20some%20other%20representative%20data%20has%20been%20filled%20in%20to%20work%20with.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2715583%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20that%20compare%20data%20in%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2715583%22%20slang%3D%22en-US%22%3Ecan%20you%20share%20a%20file%20that%20has%20actual%20values%20in%20it%20aside%20from%200.00%3F%3CBR%20%2F%3Ecreate%203%20sheets%20with%20different%20scenarios%20that%20depicts%20real%20scenarios%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2717629%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20that%20compare%20data%20in%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2717629%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047584%22%20target%3D%22_blank%22%3E%40Yea_So%3C%2FA%3E%26nbsp%3BI%20have%20entered%20some%20values%20and%20created%203%20different%20sheets.%20The%20solution%20from%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3Bseemed%20to%20help%20except%20when%20I%20select%20No%20from%20one%20of%20the%20drop-downs%20(hi-lighted%20in%20%3CFONT%20color%3D%22%23FFCC00%22%3E%3CSTRONG%3E%3CU%3Eyellow%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FFONT%3E)%20I%20get%20a%20false%20response.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2717689%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20that%20compare%20data%20in%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2717689%22%20slang%3D%22en-US%22%3ESo%20what%20do%20the%20%22Yes%22%20or%20%22No%22%20responses%20have%20to%20do%20with%20the%20cells%20that%20were%20evaluated%20by%20your%20original%20or%20my%20use%20of%20MIN%3F%20Obviously%20the%20%22No%22%20affects%20one%20or%20more%20of%20the%20values%20that%20are%20evaluated.%20YOU%20are%20the%20one%20who%20knows%20the%20interrelationships%20involved.%3C%2FLINGO-BODY%3E
New Contributor

Hello, 

I am trying to write a formula for a cell that will compare data from other cells resulting in the lowest amount in the target cell. Here's what I need: if F24 is less than C14, J14 and E18 then the result should be F24. If any of the values are lower than F24 then the value in that cell should be the result. 

Here's what I have:

=IF(F24<C14,F24,IF(F24>C14,C14,IF(F24<J14,F24,IF(F24>J14,J14,IF(F24<E18,F24,IF(F24>E18,E18))))))

It seems to work for the comparison between F24 and C14 but not for the other fields.  I should also mention that each of these fields have basic math calculations in them and some are dependent on drop downs (not sure if any of that should make a difference).

 

Any help is appreciated!

7 Replies

@gpecoraro 

 

=MIN(F24,C14,J14,E18) is a formula that will deliver the lowest of the four cells referenced.

 

It took quite a bit of hunting to find the cell in which your formula resided. If I may suggest, if you come back looking for help, it helps others help you if the spreadsheet you attach--thank you for doing that--somehow highlights where the difficulty resides AND if some other representative data has been filled in to work with.

can you share a file that has actual values in it aside from 0.00?
create 3 sheets with different scenarios that depicts real scenarios

@Yea_So I have entered some values and created 3 different sheets. The solution from @mathetes seemed to help except when I select No from one of the drop-downs (hi-lighted in yellow) I get a false response.

 

Thanks

So what do the "Yes" or "No" responses have to do with the cells that were evaluated by your original or my use of MIN? Obviously the "No" affects one or more of the values that are evaluated. YOU are the one who knows the interrelationships involved.
is the No scenario reflected in your shared workbook which also reflects the expected result?

@gpecoraro 

 

OK, tell you what. You highlighted the cells in yellow where "Yes" or "No" was requested, which, when entered, caused the cell about which you originally asked your question to not perform as desired. But when I trace back the cells that your original formula referenced (and highlighted them in green background) it turns out that some of them aren't even visible under all circumstances!!!!!!!!!!

 

And they don't all themselves yield numbers as values, so we're looking for a minimum number (by your original request) from a group of cells that don't always show numbers.

 

May I suggest you go back to the start and create a much more intelligible worksheet? If, at any rate, you want help. This is like an amusement park game where you're aiming at things in the dark. You may know what all the cryptic or non-existent labels mean, but the rest of us are not mind-readers so can't get behind them.

 

You clearly know how to write Excel formulas; what you need is some help in laying out a spreadsheet so that it makes sense, so that the various dependencies of one cell on another are evident and reasonable. As it is, it would appear that you yourself are having difficulty making sense of it, and it's not because you don't know how to write Excel formulas.

OK. Thanks for your input.