On 17/10/2007 18:37, Brian Barker wrote:
At 17:57 17/10/2007 +0100, Harold Fuchs wrote:
On 17/10/2007, Brian Barker <[EMAIL PROTECTED]> wrote:
>
> At 09:49 17/10/2007 +0100, Harold Fuchs wrote:
> >On 16/10/2007, Richard J Neuenfeldt <[EMAIL PROTECTED]> wrote:
> > > How do you simply formulate the addition of $2.50 to cells H,
I, J if
> > > cell G =50#. For example, if col G has "4/12.5 #", it does
equal 50#,
> > > and I want col H which is now $26.65 to read $29.15 . I wish
to do a
> > > one page spreadsheet of three columns.
> >
> > Before answering this we need to get straight what you mean by
"equal".
> >
> >You say "if col G has "4/12.5 #", it does equal 50#". That's not my
> >understanding of "equal".
> >
> >So, let's assume that what you really mean is "if the last three
> characters
> >of col G is either "5 #" or "50#" then add 2.5 to col H".
> >
> >Given this definition of "equal" then the formula
> >=IF(OR(RIGHT($G1;3)="50#";RIGHT($G1;3)="5 #");H1+2.5;H1) will do the
> job.
> >Copy/drag the formula down column H.
>
> This formula has a circular reference, in that it defines each cell
> in terms of itself. And the formula does not converge, so this will
> simply produce an error even with "Iterative references" switched on.
>
> The original question is not well defined, in fact ...
>
> Brian Barker
>
>
It seems to work nevertheless ...
Wow! You must have an enhanced version of Calc, which can
satisfactorily solve impossible equations such as x = x + 2.5. What's
this called, please? Where do I get a copy?
;^)
Brian Barker
Brian,
I just realised the problem. What I should have said (and what I
actually tested) is that the formula goes into columns K, L and M to
represent respectively the results for columns H, I and J. So cells in
column K get either the value from the corresponding cell in column H or
2.5 more than that depending on the value in the corresponding cell in
column G. Drag the formula down column K and across to columns L and M.
So, column K gets the formula =IF(OR(RIGHT($G1;3)="50#";RIGHT($G1;3)="5
#");H1+2.5;H1)
L gets
=IF(OR(RIGHT($G1;3)="50#";RIGHT($G1;3)="5 #");I1+2.5;I1)
and M gets
=IF(OR(RIGHT($G1;3)="50#";RIGHT($G1;3)="5 #");J1+2.5;J1)
Dragging the formula rightwards from K automatically changes the
references to column H into references to column I and then to J. We
don't want the G changing, hence the "$".
Sorry for the confusion.
--
Harold Fuchs
London, England
Please reply *only* to users@openoffice.org
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
