IF statements/formula problem

Yes I did think of suggesting that but is the cell is referenced in a
formula it will cause the formula to return #VALUE!

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"db" <[email protected]> wrote in message
news:[email protected]...
> Just a little update to this formula, you can do the same thing without
> having to format it as accounting:
>
> =IF(G28<0,"-",IF(K28>0,G28,G28/2))
>
> --
> Regards,
> db
>
>
> "Sandy Mann" wrote:
>
>> Try:
>>
>> =IF(G28<0,0,IF(K28>0,G28,G28/2))
>>
>> and format the cell as Accounting with no currency sign if necessary but
>> note that the value stored in the cell will now be zero.
>>
>> --
>> HTH
>>
>> Sandy
>> [email protected]
>> Replace@mailinator with @tiscali.co.uk
>>
>>
>> "Admiral70" <[email protected]> wrote in message
>> news:[email protected]...
>> > This works good, now what if G28 is a negative number. Is there a way
>> > to modify the following formula to not show a negative and instead show
>> > a - (dash)?
>> >
>> > =IF(K28>0,G28,G28/2)
>> >
>>
>>
>>

Just a little update to this formula, you can do the same thing without
having to format it as accounting:

=IF(G28<0,"-",IF(K28>0,G28,G28/2))

--
Regards,
db


"Sandy Mann" wrote:

> Try:
>
> =IF(G28<0,0,IF(K28>0,G28,G28/2))
>
> and format the cell as Accounting with no currency sign if necessary but
> note that the value stored in the cell will now be zero.
>
> --
> HTH
>
> Sandy
> [email protected]
> Replace@mailinator with @tiscali.co.uk
>
>
> "Admiral70" <[email protected]> wrote in message
> news:[email protected]...
> > This works good, now what if G28 is a negative number. Is there a way
> > to modify the following formula to not show a negative and instead show
> > a - (dash)?
> >
> > =IF(K28>0,G28,G28/2)
> >
>
>
>

Try:

=IF(G28<0,0,IF(K28>0,G28,G28/2))

and format the cell as Accounting with no currency sign if necessary but
note that the value stored in the cell will now be zero.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Admiral70" <[email protected]> wrote in message
news:[email protected]...
> This works good, now what if G28 is a negative number. Is there a way
> to modify the following formula to not show a negative and instead show
> a - (dash)?
>
> =IF(K28>0,G28,G28/2)
>

No, sorry, if it shows ($40.45) then I want to show just a - no # at
all, not even a zero.

Thanks for clarifying.

Ok, so the number is zero, but will show a dash. Is the dash part of
the Accounting format?

Thanks

This works good, now what if G28 is a negative number. Is there a way
to modify the following formula to not show a negative and instead show

a - (dash)?


=IF(K28>0,G28,G28/2)


Any ideas? Thanks

I'm a little confused by what you mean. Do you mean as it stands now Excel
is showing parenthesis for negative numbers? For example:

It is showing: ($40.45)
You want it to show: $-40.45

--
Regards,
db


"Admiral70" wrote:

> This works good, now what if G28 is a negative number. Is there a way
> to modify the following formula to not show a negative and instead show
> a - (dash)?
>
> =IF(K28>0,G28,G28/2)
>
>

This works good, now what if G28 is a negative number. Is there a way
to modify the following formula to not show a negative and instead show
a - (dash)?

=IF(K28>0,G28,G28/2)

Duke -
I believe you have the 2 and the 1 switched around in your formula, it
should be:

=a1/if(c1>0,1,2)

--
Regards,
db


"Duke Carey" wrote:

> =a1/if(c1>0,2,1)
>
> apologies if this came through 2 times
>
>
>
> "Admiral70" wrote:
>
> > I need to tell column B if there there is something in column C greater
> > than zero then use column A figure. Otherwise use A/2.
> >
> > A B C D
> > Savings Shared Flat fee TOTAL
> > $52.66 $26.33 $15.00 $15.00
> >
> > Can anyone help me with this formula?
> >
> > Thanks in advanced.
> >
> >

Thanks for such a quick response. I think this will work for what I
need.

Thanks again.

Admiral -
This will work for the first row, drag down for however far you need the
range to be:

=IF(C1>0,A1,A1/2)

--
Regards,
db


"Admiral70" wrote:

> I need to tell column B if there there is something in column C greater
> than zero then use column A figure. Otherwise use A/2.
>
> A B C D
> Savings Shared Flat fee TOTAL
> $52.66 $26.33 $15.00 $15.00
>
> Can anyone help me with this formula?
>
> Thanks in advanced.
>
>

=A2/if(c>0,2,1)

"Admiral70" wrote:

> I need to tell column B if there there is something in column C greater
> than zero then use column A figure. Otherwise use A/2.
>
> A B C D
> Savings Shared Flat fee TOTAL
> $52.66 $26.33 $15.00 $15.00
>
> Can anyone help me with this formula?
>
> Thanks in advanced.
>
>

Yes, in Accounting format a zero is shown as a dash.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Admiral70" <[email protected]> wrote in message
news:[email protected]...
> Ok, so the number is zero, but will show a dash. Is the dash part of
> the Accounting format?
>
> Thanks
>

=a1/if(c1>0,2,1)

apologies if this came through 2 times



"Admiral70" wrote:

> I need to tell column B if there there is something in column C greater
> than zero then use column A figure. Otherwise use A/2.
>
> A B C D
> Savings Shared Flat fee TOTAL
> $52.66 $26.33 $15.00 $15.00
>
> Can anyone help me with this formula?
>
> Thanks in advanced.
>
>

Thanks for such a quick response. I think this will work for what I
need.

Thanks again.

Try:

=IF(G28<0,0,IF(K28>0,G28,G28/2))

and format the cell as Accounting with no currency sign if necessary but
note that the value stored in the cell will now be zero.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Admiral70" <[email protected]> wrote in message
news:[email protected]...
> This works good, now what if G28 is a negative number. Is there a way
> to modify the following formula to not show a negative and instead show
> a - (dash)?
>
> =IF(K28>0,G28,G28/2)
>

=A2/if(c>0,2,1)

"Admiral70" wrote:

> I need to tell column B if there there is something in column C greater
> than zero then use column A figure. Otherwise use A/2.
>
> A B C D
> Savings Shared Flat fee TOTAL
> $52.66 $26.33 $15.00 $15.00
>
> Can anyone help me with this formula?
>
> Thanks in advanced.
>
>

Just a little update to this formula, you can do the same thing without
having to format it as accounting:

=IF(G28<0,"-",IF(K28>0,G28,G28/2))

--
Regards,
db


"Sandy Mann" wrote:

> Try:
>
> =IF(G28<0,0,IF(K28>0,G28,G28/2))
>
> and format the cell as Accounting with no currency sign if necessary but
> note that the value stored in the cell will now be zero.
>
> --
> HTH
>
> Sandy
> [email protected]
> Replace@mailinator with @tiscali.co.uk
>
>
> "Admiral70" <[email protected]> wrote in message
> news:[email protected]...
> > This works good, now what if G28 is a negative number. Is there a way
> > to modify the following formula to not show a negative and instead show
> > a - (dash)?
> >
> > =IF(K28>0,G28,G28/2)
> >
>
>
>

No, sorry, if it shows ($40.45) then I want to show just a - no # at
all, not even a zero.

Thanks for clarifying.

Yes, in Accounting format a zero is shown as a dash.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Admiral70" <[email protected]> wrote in message
news:[email protected]...
> Ok, so the number is zero, but will show a dash. Is the dash part of
> the Accounting format?
>
> Thanks
>

This works good, now what if G28 is a negative number. Is there a way
to modify the following formula to not show a negative and instead show

a - (dash)?


=IF(K28>0,G28,G28/2)


Any ideas? Thanks

=a1/if(c1>0,2,1)

apologies if this came through 2 times



"Admiral70" wrote:

> I need to tell column B if there there is something in column C greater
> than zero then use column A figure. Otherwise use A/2.
>
> A B C D
> Savings Shared Flat fee TOTAL
> $52.66 $26.33 $15.00 $15.00
>
> Can anyone help me with this formula?
>
> Thanks in advanced.
>
>

Ok, so the number is zero, but will show a dash. Is the dash part of
the Accounting format?

Thanks

I'm a little confused by what you mean. Do you mean as it stands now Excel
is showing parenthesis for negative numbers? For example:

It is showing: ($40.45)
You want it to show: $-40.45

--
Regards,
db


"Admiral70" wrote:

> This works good, now what if G28 is a negative number. Is there a way
> to modify the following formula to not show a negative and instead show
> a - (dash)?
>
> =IF(K28>0,G28,G28/2)
>
>

This works good, now what if G28 is a negative number. Is there a way
to modify the following formula to not show a negative and instead show
a - (dash)?

=IF(K28>0,G28,G28/2)

Admiral -
This will work for the first row, drag down for however far you need the
range to be:

=IF(C1>0,A1,A1/2)

--
Regards,
db


"Admiral70" wrote:

> I need to tell column B if there there is something in column C greater
> than zero then use column A figure. Otherwise use A/2.
>
> A B C D
> Savings Shared Flat fee TOTAL
> $52.66 $26.33 $15.00 $15.00
>
> Can anyone help me with this formula?
>
> Thanks in advanced.
>
>

Yes I did think of suggesting that but is the cell is referenced in a
formula it will cause the formula to return #VALUE!

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"db" <[email protected]> wrote in message
news:[email protected]...
> Just a little update to this formula, you can do the same thing without
> having to format it as accounting:
>
> =IF(G28<0,"-",IF(K28>0,G28,G28/2))
>
> --
> Regards,
> db
>
>
> "Sandy Mann" wrote:
>
>> Try:
>>
>> =IF(G28<0,0,IF(K28>0,G28,G28/2))
>>
>> and format the cell as Accounting with no currency sign if necessary but
>> note that the value stored in the cell will now be zero.
>>
>> --
>> HTH
>>
>> Sandy
>> [email protected]
>> Replace@mailinator with @tiscali.co.uk
>>
>>
>> "Admiral70" <[email protected]> wrote in message
>> news:[email protected]...
>> > This works good, now what if G28 is a negative number. Is there a way
>> > to modify the following formula to not show a negative and instead show
>> > a - (dash)?
>> >
>> > =IF(K28>0,G28,G28/2)
>> >
>>
>>
>>

Duke -
I believe you have the 2 and the 1 switched around in your formula, it
should be:

=a1/if(c1>0,1,2)

--
Regards,
db


"Duke Carey" wrote:

> =a1/if(c1>0,2,1)
>
> apologies if this came through 2 times
>
>
>
> "Admiral70" wrote:
>
> > I need to tell column B if there there is something in column C greater
> > than zero then use column A figure. Otherwise use A/2.
> >
> > A B C D
> > Savings Shared Flat fee TOTAL
> > $52.66 $26.33 $15.00 $15.00
> >
> > Can anyone help me with this formula?
> >
> > Thanks in advanced.
> >
> >

Ok, so the number is zero, but will show a dash. Is the dash part of
the Accounting format?

Thanks

Yes, in Accounting format a zero is shown as a dash.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Admiral70" <[email protected]> wrote in message
news:[email protected]...
> Ok, so the number is zero, but will show a dash. Is the dash part of
> the Accounting format?
>
> Thanks
>

Yes I did think of suggesting that but is the cell is referenced in a
formula it will cause the formula to return #VALUE!

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"db" <[email protected]> wrote in message
news:[email protected]...
> Just a little update to this formula, you can do the same thing without
> having to format it as accounting:
>
> =IF(G28<0,"-",IF(K28>0,G28,G28/2))
>
> --
> Regards,
> db
>
>
> "Sandy Mann" wrote:
>
>> Try:
>>
>> =IF(G28<0,0,IF(K28>0,G28,G28/2))
>>
>> and format the cell as Accounting with no currency sign if necessary but
>> note that the value stored in the cell will now be zero.
>>
>> --
>> HTH
>>
>> Sandy
>> [email protected]
>> Replace@mailinator with @tiscali.co.uk
>>
>>
>> "Admiral70" <[email protected]> wrote in message
>> news:[email protected]...
>> > This works good, now what if G28 is a negative number. Is there a way
>> > to modify the following formula to not show a negative and instead show
>> > a - (dash)?
>> >
>> > =IF(K28>0,G28,G28/2)
>> >
>>
>>
>>

I need to tell column B if there there is something in column C greater
than zero then use column A figure. Otherwise use A/2.

A B C D
Savings Shared Flat fee TOTAL
$52.66 $26.33 $15.00 $15.00

Can anyone help me with this formula?

Thanks in advanced.

Just a little update to this formula, you can do the same thing without
having to format it as accounting:

=IF(G28<0,"-",IF(K28>0,G28,G28/2))

--
Regards,
db


"Sandy Mann" wrote:

> Try:
>
> =IF(G28<0,0,IF(K28>0,G28,G28/2))
>
> and format the cell as Accounting with no currency sign if necessary but
> note that the value stored in the cell will now be zero.
>
> --
> HTH
>
> Sandy
> [email protected]
> Replace@mailinator with @tiscali.co.uk
>
>
> "Admiral70" <[email protected]> wrote in message
> news:[email protected]...
> > This works good, now what if G28 is a negative number. Is there a way
> > to modify the following formula to not show a negative and instead show
> > a - (dash)?
> >
> > =IF(K28>0,G28,G28/2)
> >
>
>
>

Try:

=IF(G28<0,0,IF(K28>0,G28,G28/2))

and format the cell as Accounting with no currency sign if necessary but
note that the value stored in the cell will now be zero.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Admiral70" <[email protected]> wrote in message
news:[email protected]...
> This works good, now what if G28 is a negative number. Is there a way
> to modify the following formula to not show a negative and instead show
> a - (dash)?
>
> =IF(K28>0,G28,G28/2)
>

No, sorry, if it shows ($40.45) then I want to show just a - no # at
all, not even a zero.

Thanks for clarifying.

This works good, now what if G28 is a negative number. Is there a way
to modify the following formula to not show a negative and instead show

a - (dash)?


=IF(K28>0,G28,G28/2)


Any ideas? Thanks

I'm a little confused by what you mean. Do you mean as it stands now Excel
is showing parenthesis for negative numbers? For example:

It is showing: ($40.45)
You want it to show: $-40.45

--
Regards,
db


"Admiral70" wrote:

> This works good, now what if G28 is a negative number. Is there a way
> to modify the following formula to not show a negative and instead show
> a - (dash)?
>
> =IF(K28>0,G28,G28/2)
>
>

This works good, now what if G28 is a negative number. Is there a way
to modify the following formula to not show a negative and instead show
a - (dash)?

=IF(K28>0,G28,G28/2)

Duke -
I believe you have the 2 and the 1 switched around in your formula, it
should be:

=a1/if(c1>0,1,2)

--
Regards,
db


"Duke Carey" wrote:

> =a1/if(c1>0,2,1)
>
> apologies if this came through 2 times
>
>
>
> "Admiral70" wrote:
>
> > I need to tell column B if there there is something in column C greater
> > than zero then use column A figure. Otherwise use A/2.
> >
> > A B C D
> > Savings Shared Flat fee TOTAL
> > $52.66 $26.33 $15.00 $15.00
> >
> > Can anyone help me with this formula?
> >
> > Thanks in advanced.
> >
> >

Thanks for such a quick response. I think this will work for what I
need.

Thanks again.

Admiral -
This will work for the first row, drag down for however far you need the
range to be:

=IF(C1>0,A1,A1/2)

--
Regards,
db


"Admiral70" wrote:

> I need to tell column B if there there is something in column C greater
> than zero then use column A figure. Otherwise use A/2.
>
> A B C D
> Savings Shared Flat fee TOTAL
> $52.66 $26.33 $15.00 $15.00
>
> Can anyone help me with this formula?
>
> Thanks in advanced.
>
>

=a1/if(c1>0,2,1)

apologies if this came through 2 times



"Admiral70" wrote:

> I need to tell column B if there there is something in column C greater
> than zero then use column A figure. Otherwise use A/2.
>
> A B C D
> Savings Shared Flat fee TOTAL
> $52.66 $26.33 $15.00 $15.00
>
> Can anyone help me with this formula?
>
> Thanks in advanced.
>
>

=A2/if(c>0,2,1)

"Admiral70" wrote:

> I need to tell column B if there there is something in column C greater
> than zero then use column A figure. Otherwise use A/2.
>
> A B C D
> Savings Shared Flat fee TOTAL
> $52.66 $26.33 $15.00 $15.00
>
> Can anyone help me with this formula?
>
> Thanks in advanced.
>
>