Re: Complex Calculations

[Ralph Shumaker <rafazap cwnet com>]

John Machin wrote:
> 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.
>   

I based a reasonable assumption upon the info provided.  I do not make 
assumptions about the logic of someone's layout.  Besides, without 
actually knowing his layout, I was perfectly fine since I said "Let's 
assume that you meant ..."  It may not be the layout that you or I would 
choose, but that does not make it wrong.

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

En-Fn will give you the difference.  sum(En-Fn) is different.  He used 
sum().  I merely assumed that he knew that that was what he wanted.  I 
did not presume to know his column headings.

Whether I was spot on or not, and whether you were spot on or not, Fred 
said "Any and all suggestions will be greatly appreciated."

So I'm sure that between all the replies, he will be able to glean what 
he needs and tailor it to suit his goal.