Merging 2 Formulas

Discuss the spreadsheet application

Merging 2 Formulas

Postby AndyPorter » Thu Oct 10, 2019 11:48 am

Hi,

I use the following formula to look up whether or not the contents of specified cells fall between the parameters in a look up table.

Code: Select all   Expand viewCollapse view
=IF(ISERROR(IF(OR(T769<VLOOKUP(V769;$AB$5:$AD$26;2;0);U769>VLOOKUP(V769;$AB$5:$AD$26;3;0));1;0));"";IF(OR(T769<VLOOKUP(V769;$AB$5:$AD$26;2;0);U769>VLOOKUP(V769;$AB$5:$AD$26;3;0));1;0))


Columns T and U contain a time and column V contains a name. Likewise columns AB and AD contain reference times and AD contains the reference name.

This formula works perfectly however I now need to amend it so that if the name in column V equals 'Floor Sweep', 'Floor Wash', 'Toilets', 'Restaurant' or 'Rubbish / Spillages' it returns 0. If the name equals something else the formula would then continue as per the original formula outlined above.

Your help will be greatly appreciated!
Last edited by robleyd on Thu Oct 10, 2019 12:05 pm, edited 1 time in total.
Reason: Add code tags
OpenOffice 2.4.2
Windows 7
AndyPorter
 
Posts: 4
Joined: Mon Dec 15, 2014 12:46 am

Re: Merging 2 Formulas

Postby FJCC » Thu Oct 10, 2019 4:10 pm

Try this formula. I changed the error check to use the MATCH function since I think you just want to confirm that V769 appears in the range AB5:AB26.
Code: Select all   Expand viewCollapse view
IF(ISNUMBER(MATCH(V769;{"Floor Sweep";"Floor Wash";"Toilets";"Restaurant";"Rubbish / Spillages"};0));0; IF(ISERROR(MATCH(V769;$AB$5:$AB$26;0));"";IF(OR(T769<VLOOKUP(V769;$AB$5:$AD$26;2;0);U769>VLOOKUP(V769;$AB$5:$AD$26;3;0));1;0))
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7316
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Merging 2 Formulas

Postby FJCC » Thu Oct 10, 2019 4:22 pm

By the way, since OR() returns TRUE or FALSE and TRUE = 1 and FALSE = 0, you can get rid of that last IF().
Code: Select all   Expand viewCollapse view
IF(ISNUMBER(MATCH(V769;{"Floor Sweep";"Floor Wash";"Toilets";"Restaurant";"Rubbish / Spillages"};0));0; IF(ISERROR(MATCH(V769;$AB$5:$AB$26;0));"";OR(T769<VLOOKUP(V769;$AB$5:$AD$26;2;0);U769>VLOOKUP(V769;$AB$5:$AD$26;3;0)))
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7316
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA


Return to Calc

Who is online

Users browsing this forum: raynold3228 and 20 guests