excel - Use an array formula to check if a similar contract ended in the last 12 months -

-

i'm troubleshooting report spreadsheet i've written. logic had written isn't working, thought i'd see if ask here.

essentially, have list of contracts, list of products, list of end , start dates, , various other pieces of information. need know if product of renewal using information.

example data:

product   company number    start of contract        end of contract     contract id     include in month's report fax            1234             10.09.2013              10.09.2014           1                      no fax            1234             10.09.2014              10.09.2015           2                      no      box            5678             11.01.2014              30.04.2015           3                      no box            5678             01.05.2015              11.01.2016           4                     yes fax            5678             01.05.2015              01.05.2016           5                     yes cup            9876             03.05.2015              03.05.2016           6                     yes

what want work out using data whether file new product, new business, or renewal of existing file.

the logic behind whether contract should included in month's report simple - check if start date month requested elsewhere in report.

in example given above,

  • contract id's 1,3 , 6 new business
  • id's 2 , 4 renewals
  • id 5 new product, existing business company

i've worked out basic way of working out month if there new product file, because company number won't unique, product will. i'd make better though, because if done is, return "false" first occurrence of product has subsequently been renewed.

what need find way of checking if there previous contract product listed, contracts new clients, , contracts there previous contract wasn't same product. in essence, need logic below answers please!

results:

contract id     renewal?        new product?         new business?     1              no                  no                     yes     2             yes                  no                     no     3              no                  no                     yes     4             yes                  no                     no     5              no                 yes                     no     6              no                  no                     yes

i've got few helper columns far, "ended in last 12 months", , "number of agreements". keep getting turned around when comes logic 3 final results!

renewal - there instance of same product , company earlier start date

=countifs(a$2:a$7,a2,b$2:b$7,b2,d$2:d$7,"<"&d2)>0

new product - there no instance of same product & company earlier start date, there instance of product & same company earlier start date

=and(countifs(a$2:a$7,a2,b$2:b$7,b2,d$2:d$7,"<"&d2)=0,countifs(a$2:a$7,"<>"&a2,b$2:b$7,b2,d$2:d$7,"<"&d2)>0)

new business - there no instance of same company earlier start date

=countifs($b$2:$b$7,b2,$d$2:$d$7,"<"&d2)=0

enter image description here


Comments

Post a Comment

Popular posts from this blog

Java 3D LWJGL collision -

-
i making 3d java game lwjgl library, , wondering how add collision detection, player not go through models. i using obj models. here objloader class, loads models: package renderengine; import java.io.bufferedreader; import java.io.file; import java.io.filenotfoundexception; import java.io.filereader; import java.util.arraylist; import java.util.list; import models.rawmodel; import org.lwjgl.util.vector.vector2f; import org.lwjgl.util.vector.vector3f; public class objloader { public static rawmodel loadobjmodel(string filename, loader loader){ filereader fr = null; try { fr = new filereader(new file("res/"+filename+".obj")); } catch (filenotfoundexception e) { system.err.println("couldn't load file!"); e.printstacktrace(); } bufferedreader reader = new bufferedreader(fr); string line; list<vector3f> vertices = new arraylist<vector3f...
Read more

spring - SubProtocolWebSocketHandler - No handlers -

-
i have ugly error during deploying spring app on jboss. 18:11:16,025 error [org.apache.catalina.core.containerbase.[jboss.web].[default-host].[/consumer]] (msc service thread 1-7) exception sending context initialized event listener instance of class org.springframework.web.context.contextloaderlistener: org.springframework.context.applicationcontextexception: failed start bean 'subprotocolwebsockethandler'; nested exception java.lang.illegalargumentexception: no handlers @ org.springframework.context.support.defaultlifecycleprocessor.dostart(defaultlifecycleprocessor.java:176) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor.access$200(defaultlifecycleprocessor.java:51) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor$lifecyclegroup.start(defaultlifecycleprocessor.java:346) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.s...
Read more

methods - python can't use function in submodule -

-
my folder structure this: pythonstuff/ program.py modulea/ __init__.py foo.py bar.py here's code bar.py : def hello(): print("hello, world!") here's code program.py : #!/usr/bin/env python3 modulea import bar bar.hello() i run $ python3 program.py somehow error: file "program.py", line 3, in <module> bar.hello() attributeerror: 'module' object has no attribute 'hello' edit: __init__.py file empty. edit2: after trying realized had bar.py in root directory contained hello() method. bar.py in modulea/ directory empty. add it import os __all__ = [] module in os.listdir(os.path.dirname(__file__)): if module != '__init__.py' , module[-3:] == '.py': __all__.append(module[:-3]) the init .py files required make python treat directories containing packages; done prevent directories common name, such string, unintentionally hiding v...
Read more