Re: Complex Calculations

[John Machin <sjmachin lexicon net>]

Ralph Shumaker wrote:
> Norman Frederick wrote:
> > Thanks, John Machin for reply. What I am trying to do is:
> > Column C and D are dates.
> > Column D and F are dollar numbers.
> > Column G is less than one year - 365 days.
> > Column H is greater than one year - 365 days.

Fred, you really need to make up your mind whether you need "a 365-day 
period" or a "year, allowing for leap years".


> > The operation I want to perform is:
> >
> > IF datedif is less than 365 G =sum(Dn - Fn) else H=sum(Dn - Fn)
> >
> >
> > Can this be done?

Of course.

> > Will it require a formula or an executable 
> > script(which I do not yet know how to write)?

Two trivial formulas.

> > Any and all suggestions will be greatly appreciated.
> > I will learn!

Get yourself a tutorial book on using spreadsheet programs.

> > Fred

> >   
> First of all, you have column D being both a date and a dollar amount.  
> So let's assume that you meant that column E is the dollar amount.
>
> If C and D are both in date format then you should be able to do it like 
> this:
> In cell Gn:
> =if(Dn-Cn<365,sum(En-Fn),)
> In cell Hn:
> =if(Dn-Cn<365,,sum(En-Fn))

Firstly, this would imply that Fred's columns C to F are headed 
something like:
buy date / sell date / sell amt / buy amt
which is not a logical/sensible order.

Secondly, you don't need sum(En-Fn) ... just En-Fn is enough.