I have a report which is imported from external data and comprises of
relevant Sales orders and purchase orders for specific product items. The
data when in the file format does not show negative figures. I would like to
use Excel to look at say cell A1 and if this is an SOR* then for the returned
figure to be *-1 which would then allow me to distinguish between the
positive and negative thus producing a bottom line amount of the exact
product item stock figure. I have tried to use the IF function but all that
is returned are blank cells. So far I've tried: =IF($A1=SOR*,H1*-1,"") which
seemed favourite but doesn't work, which function should I be using?
Thanks

Assuming these are hard coded values and not wildcard expressions

IF(A1="SOR*","*-1","")

I don't know why H1 was there, formulas can only return result in the cells
that hold them so if you meant that H1 should return *-1 then you must put
the formula in H1


--
Regards,

Peo Sjoblom


"deniseh" wrote in message
...
I have a report which is imported from external data and comprises of
relevant Sales orders and purchase orders for specific product items. The
data when in the file format does not show negative figures. I would like
to
use Excel to look at say cell A1 and if this is an SOR* then for the
returned
figure to be *-1 which would then allow me to distinguish between the
positive and negative thus producing a bottom line amount of the exact
product item stock figure. I have tried to use the IF function but all
that
is returned are blank cells. So far I've tried: =IF($A1=SOR*,H1*-1,"")
which
seemed favourite but doesn't work, which function should I be using?
Thanks

Sorry, I am using wildcard characters and would like my result to appear in
cell J1 so that the value of a POR is unchanged but an SOR would be changed
from the original value to its negative equivalent.
POR Description Supp OrdDate DueDate StockRef QtyOrd QtyOS
POR001424 GT Air Bike Grey 2008 SOUTHE 20070806 20070806 BYGTB8AIR2 100 100
POR001468 GT Air Bike Grey 2008 SOUTHE 20070819 20070819 BYGTB8AIR2 200 200
POR001469 GT Air Bike Grey 2008 SOUTHE 20070923 20070923 BYGTB8AIR2 200 200
POR001481 GT Air Bike Grey 2008 SOUTHE 20071118 20071118 BYGTB8AIR2 200 200
POR001482 GT Air Bike Grey 2008 SOUTHE 20071020 20071020 BYGTB8AIR2 100 100
SOR075437 GT Air Bike Grey 2008 DAVEME 20070727 20070727 BYGTB8AIR2 1 1
POR001402 GT Bump Bike Blue 2008 SOUTHE 20070725 20070725 BYGTB8BUM2 125 125
POR001424 GT Bump Bike Blue 2008 SOUTHE 20070806 20070806 BYGTB8BUM2 75 75
POR001468 GT Bump Bike Blue 2008 SOUTHE 20070819 20070819 BYGTB8BUM2 25 25
POR001469 GT Bump Bike Blue 2008 SOUTHE 20070923 20070923 BYGTB8BUM2 150 150
POR001481 GT Bump Bike Blue 2008 SOUTHE 20071118 20071118 BYGTB8BUM2 125 125
POR001482 GT Bump Bike Blue 2008 SOUTHE 20071020 20071020 BYGTB8BUM2 100 100
SOR072589 GT Bump Bike Blue 2008 VALE 20070618 20070618 BYGTB8BUM2 1 1
SOR072757 GT Bump Bike Blue 2008 BARNSL 20070618 20070618 BYGTB8BUM2 1 1
This is an example of the data I have (Sorry not in Excel format) are you
able to distinguish the cell values from this?
Thanks
"deniseh" wrote:

I have a report which is imported from external data and comprises of
relevant Sales orders and purchase orders for specific product items. The
data when in the file format does not show negative figures. I would like to
use Excel to look at say cell A1 and if this is an SOR* then for the returned
figure to be *-1 which would then allow me to distinguish between the
positive and negative thus producing a bottom line amount of the exact
product item stock figure. I have tried to use the IF function but all that
is returned are blank cells. So far I've tried: =IF($A1=SOR*,H1*-1,"") which
seemed favourite but doesn't work, which function should I be using?
Thanks

Try

=IF(ISNUMBER(SEARCH("SOR",A1)),-1*H1,"")

I assume H1 is the value you want to change to negative, if not replace H1
with that cell reference. What the above does is that if A1 contains SOR
then change then value in H1 to negative. If it would always start with
SOR/POR

=IF(LEFT(A1,3)="SOR",-1*H1,"")


note that if you get a value error then the value in H1 is not numerical


--
Regards,

Peo Sjoblom



"deniseh" wrote in message
...
Sorry, I am using wildcard characters and would like my result to appear
in
cell J1 so that the value of a POR is unchanged but an SOR would be
changed
from the original value to its negative equivalent.
POR Description Supp OrdDate DueDate StockRef QtyOrd QtyOS
POR001424 GT Air Bike Grey 2008 SOUTHE 20070806 20070806 BYGTB8AIR2 100
100
POR001468 GT Air Bike Grey 2008 SOUTHE 20070819 20070819 BYGTB8AIR2 200
200
POR001469 GT Air Bike Grey 2008 SOUTHE 20070923 20070923 BYGTB8AIR2 200
200
POR001481 GT Air Bike Grey 2008 SOUTHE 20071118 20071118 BYGTB8AIR2 200
200
POR001482 GT Air Bike Grey 2008 SOUTHE 20071020 20071020 BYGTB8AIR2 100
100
SOR075437 GT Air Bike Grey 2008 DAVEME 20070727 20070727 BYGTB8AIR2 1 1
POR001402 GT Bump Bike Blue 2008 SOUTHE 20070725 20070725 BYGTB8BUM2 125
125
POR001424 GT Bump Bike Blue 2008 SOUTHE 20070806 20070806 BYGTB8BUM2 75 75
POR001468 GT Bump Bike Blue 2008 SOUTHE 20070819 20070819 BYGTB8BUM2 25 25
POR001469 GT Bump Bike Blue 2008 SOUTHE 20070923 20070923 BYGTB8BUM2 150
150
POR001481 GT Bump Bike Blue 2008 SOUTHE 20071118 20071118 BYGTB8BUM2 125
125
POR001482 GT Bump Bike Blue 2008 SOUTHE 20071020 20071020 BYGTB8BUM2 100
100
SOR072589 GT Bump Bike Blue 2008 VALE 20070618 20070618 BYGTB8BUM2 1 1
SOR072757 GT Bump Bike Blue 2008 BARNSL 20070618 20070618 BYGTB8BUM2 1 1
This is an example of the data I have (Sorry not in Excel format) are you
able to distinguish the cell values from this?
Thanks
"deniseh" wrote:

I have a report which is imported from external data and comprises of
relevant Sales orders and purchase orders for specific product items.
The
data when in the file format does not show negative figures. I would
like to
use Excel to look at say cell A1 and if this is an SOR* then for the
returned
figure to be *-1 which would then allow me to distinguish between the
positive and negative thus producing a bottom line amount of the exact
product item stock figure. I have tried to use the IF function but all
that
is returned are blank cells. So far I've tried: =IF($A1=SOR*,H1*-1,"")
which
seemed favourite but doesn't work, which function should I be using?
Thanks

Thanks, that worked a treat, now my Pivot Table finally makes sense. Thanks
again

"deniseh" wrote:

I have a report which is imported from external data and comprises of
relevant Sales orders and purchase orders for specific product items. The
data when in the file format does not show negative figures. I would like to
use Excel to look at say cell A1 and if this is an SOR* then for the returned
figure to be *-1 which would then allow me to distinguish between the
positive and negative thus producing a bottom line amount of the exact
product item stock figure. I have tried to use the IF function but all that
is returned are blank cells. So far I've tried: =IF($A1=SOR*,H1*-1,"") which
seemed favourite but doesn't work, which function should I be using?
Thanks