On 17/10/2007 21:43, Charlie Seaman wrote:
Harold Fuchs wrote:
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.
It looks to me like Mr.Neuenfeldt is representing a quantity and
package size in column G and wanting to test the result of quantity
(in this case 4) times package size (in this case 12.5 lbs) is
greater than 50 lbs. If this is true I would suggest that Mr.
Neuenfeldt change column G to two separate columns, one for the
quantity and one for the package size (or weight) so the test can be
done with math instead of comparing text.
Is he getting these messages?
Given that he's on the CC: list, I'd say "yes".
--
Harold Fuchs
London, England
Please reply *only* to users@openoffice.org
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
