Formula Too Big For Nesting

  • Hi - please feel free to change my title, or point me towards an already created answer - I know what I want to get out at the other end, but not how to express (succinctly!) how to do it!


    Part of my job involves analysis of bids that we are winning/losing, and how this is changing over time.


    I have a spreadsheet with overview information of a bid including the following details:
    Date the bid came in, Bid Sector (we have different business units). The bid information is on different tabs depending on whether the bid is current, no bid, lost or won. Within these tabs is then the information on what stage we are at/lost/won etc the bid.


    I have attached a cut down version of this spreadsheet (I've had to take out commercially confidential info, and also take out the no-bid tab, as this put it over the file size limit.)


    I need to create two tables (that I can then create graphs etc from);
    (1) showing a count of bids across all tabs by month that the bid came in (i.e. July 07; August 07 etc) broken down by business sector.
    (2) showing a count of bids across all tabs by month that the bid came in broken down by current stage (i.e. PQQ/ITT/Lost (PQQ)/Lost (Demo)/Won etc)


    Unfortunately, my (horribly complicated!) plan to run the date part of this through nested IFs has been stimied by the fact that I've got over 12 months in there (and this spreadsheet is only going to grow over the next X years...)


    Running a pivot off of the data doesn't work, either, as it looks at the full date, not the months. I have to have the full date in there (i.e. dd/mm/yyyy rather than mm/yyyy), as another part of the analysis is looking at how long it takes for a bid to go from coming in to being lost/won.


    So - my plea is to anyone who can give me a hand with sorting this out! :)


    Many thanks in advance for your help.

  • Re: Formula Too Big For Nesting


    If you wanted a quick fix to this just create an extra column on each sheet with a formula in that gives you Year and Month in the format YYYYMM then you could easily maintain the data you need with pivot tables...


    Formula would be (assuming row 2 with date in column D);
    =YEAR(D2)&TEXT(MONTH(D2),"00")


    Cheers,
    Ian

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!