An accounting analyst's currency-cleaning chain returns the wrong sign on every negative value. Their original formula:
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),"(",""),")",""),",",""))
It correctly handles positive values like "$1,234.56" → 1234.56. But finance convention encodes negatives as parenthesized values: "($500.00)" should produce -500, not 500. The original formula strips the parens and the sign goes with them — every negative comes out positive.
The fix: detect the parens before stripping them and apply a sign multiplier:
=IF(LEFT(A2,1)="(", -1, 1) * VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),"(",""),")",""),",",""))
The leading-character check returns -1 for parenthesized values (negatives) and 1 for everything else; the multiplication restores the sign.
Your task:
- In column B (Numeric Value), write a corrected formula that returns the right signed number for every row.
- The formula must drag-fill from B2 → B13.
Graded cells: B3 (($500.00) → -500), B5 (($1,000.00) → -1000), B7 (($350.50) → -350.5).
Open this problem on a desktop to attempt it.