Converting a Value

[Bullie]

Can some one help, I have a spreadsheet which contains text in on
column describing the transaction is it a credit or invoice.

I would like to see what the transaction type is and if a Credit the
convert the values in Column D / E to minus Values. Example o
structure

C D E
INV 10 20 would like Column F / G to show 10
20
CRN 10 20 would like Column F / G to show -10
-20


Please help

 

[Arvi Laanemets]

Hi

Into F2
=IF($C2="CRN",-1,1)*D2
and copy F2 to whole range in F:G

When you want to have the formula to be ready for new inputs in C:E, then
use for F2
=IF(OR(C2="",D2=""),"",IF($C2="CRN",-1,1)*D2)
and for G2
=IF(OR(C2="",E2=""),"",IF($C2="CRN",-1,1)*E2)

 

[theillknight]

Say C1 is the first cell with the 'inv' or 'crn', try in F1:

=if(c1="INV",d1,-d1)

and G1

=if(c1="INV",e1,-d1)

Or to be even more specific:

=if(c1="INV",d1,if(c1="CRN",-d1))

and

=if(c1="INV",e1,if(c1="CRN",-e1)

 

[Bullie]

Thanks it worked a treat.

Ad