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?
--
Thanks.
Yours,
**//Charlie
//***/Charles D. Seaman/**/
/* 770-617-1214
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]