Wrapping a selection of cells containing formulas and constants within an IF statement

[sps21]

Hi there

I am trying to introduce a toggle in my financial model wherein if the toggle is OFF then a of the business unit's P&L and BS wont be counted towards the consolidated total. I already have the P&L and B&S in the excel and now I have to introduce the IF statement around the driver cells to reflect this toggle. Is there a quicker way to do this without having to manually go and wrap the individual cells in and IF statement?

Thanks
SPS

 

[Gerald Higgins]

Hi, I've read your post a couple of times, your request seems FAR too vague for anyone to have a chance of answering it.

Can you describe exactly what you want to do please ?

Don't spend too much time explaining the real world application of your situation, just focus on exactly what data and formulas you have now, and what exactly you want to do with them.

 

[sps21]

Hi, I've read your post a couple of times, your request seems FAR too vague for anyone to have a chance of answering it.

Can you describe exactly what you want to do please ?

Don't spend too much time explaining the real world application of your situation, just focus on exactly what data and formulas you have now, and what exactly you want to do with them.

Hi Gerald

Thanks for the feedback! let me try again!

I am modelling a company with 5 subsidiaries and I have to introduce an option of viewing the consolidated P&L and B&S of the company with/without a particular subsidiary. So I want to introduce a toggle cell which takes value of 1 and 0 and based on the value which i choose it will show/hide the financials of the subsidiary.

For eg say

D4 = 0 or 1

Now in revenue line item for 2017 i will have the formula

E10 = IF( $D$4=1, 500000,0).

Now the issue is this subsidiary P&L and Balance Sheet has lot of lines and I want to wrap the above mentioned formula in each of the cell.

Another example say

Gross Margin has formula in E21 = E18/E10 and I want it to be E21 = If($D$4=1, E18/E10,0).

Hope this makes things more clear.

Thanks

SPS

 

[Gerald Higgins]

Ah yes, I see, that is a bit clearer.

I notice that the two examples you gave are a little different from each other.

E10 looks like it CURRENTLY contains just a single value, 500000, while E21 contains a formula.

Have I got this right ?

If you want to apply this =if($D$4=1 . . . condition to every cell in a specified range, you might be able to do this by converting the formulas to text strings, then use concatenation formulas to add the new elements of the formula to the beginning and end of each entry, and then convert the text strings back to formulas.

Am I on the right lines here ?

 

[sps21]

Ah yes, I see, that is a bit clearer.

I notice that the two examples you gave are a little different from each other.

E10 looks like it CURRENTLY contains just a single value, 500000, while E21 contains a formula.

Have I got this right ?

If you want to apply this =if($D$4=1 . . . condition to every cell in a specified range, you might be able to do this by converting the formulas to text strings, then use concatenation formulas to add the new elements of the formula to the beginning and end of each entry, and then convert the text strings back to formulas.

Am I on the right lines here ?

Hi Gerald

Yes that's exactly what I want. Could you please show me how its done by an example?

Thanks

SPS

 

[Gerald Higgins]

Hi, in my opinion there's no need to quote each entire post, it just makes the thread un-necessarily long

So, here goes.

IMPORTANT - DO THIS ON A COPY OF YOUR DATA, JUST IN CASE IT GOES WRONG !

STEP 1.
Select the range containing all the cells you want to change.
Use Edit, Find and Replace, to FIND "=" and REPLACE with "AAA="
By the way leave out the " characters.
This will convert all your formulas to text strings starting with AAA.
If the string AAA happens to already occur in your data for some other reason, use a different string, such as XYZ, it doesn't really matter what string you use.

STEP 2
Set up another blank area on your worksheet, which we will use to create copies of your existing formulas.
Let's say that your current data, both formulas and single values, are in the range A1:Z100.
So let's say we'll use the range AA1:AZ100, which is currently empty, to create copies of all your data.
In AA1, insert this formula
=IF(A1="","",IF(LEFT(A1,3)="AAA",A1,"AAA=if($D$4=1,"&A1))
Remember to replace "AAA" with your other string, IF you're using another string.
IF ALL the cells in the source range are NOT BLANK, you can simplify this formula slightly if you want, but it's not important.
Copy this formula to the entire range AA1:AZ100.

STEP 3
Set up ANOTHER empty range, let's say BA1:BZ100.
In BA1, input this formula
=IF(AA1="","",AA1&",0)")
and copy to the entire range BA1:BZ100

STEP 4
Copy the entire range BA1:BZ100 to A1, and use Paste Special, Values
Now if you like, you can delete everything in AA1:AZ100 and also BA1:BZ100

STEP 5
Select the entire range A1:Z100, and use Edit, Find and Replace, to FIND "AAA=" and replace with "=" (again leaving out the " characters).

And that should be it.

 

[sps21]

Hi Gerald

Thats awesome! Thanks for the tip!

Sps