# Re: Complex Calculations

• From: Ralph Shumaker <rafazap cwnet com>
• To: gnumeric-list gnome org
• Subject: Re: Complex Calculations
• Date: Mon, 25 Feb 2008 10:27:34 -0800

```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.
```

```