Re: Complex Calculations

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.


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:
In cell Hn:

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.

[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]