Forum Discussion

WGCSDT's avatar
WGCSDT
Copper Contributor
Jan 09, 2019

Formula Help!!

Good afternoon. I am somewhat knowledgeable in excel but I am having trouble writing a formula.  I have a rental company with a large quantity of units along with several types of units. I do contrac...
excel
Formulas and Functions
IngeborgHawighorst's avatar
IngeborgHawighorst
MVP
Jan 09, 2019

Hello,

 

that question is really hard to answer without seeing how your data is organised. Do you have several units of the same model? How is the data stored?

 

Can you post a sample file with just a few items that helps us understand your data? Just a few types of units and a few rentals in the same setup as your original data would be helpful.

  • WGCSDT's avatar
    WGCSDT
    Copper Contributor
    Jan 09, 2019

    Yes I have several units of the same type. Attached is a sample of what I have. thank you

      • IngeborgHawighorst's avatar
        IngeborgHawighorst
        MVP
        Jan 09, 2019

        Hi, I started this answer earlier, but forgot to click the POST button.

         

        May I suggest a different layout?

         

         

        The formula in cel V3 is

         

        =V$1-SUMPRODUCT(--($B$3:$B$12<$U3),--($C$3:$C$12>$U3),D$3:D$12)

         

        Copy across and down. Then enter the start date in U3 and in the next rows use formulas to increment by one day per row. Copy down as many rows as you need and make sure to adjust to the source data in columns C to M

         

        cheers,