• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is there any way for formulas to ignore certain formatting?

P

PipBoy808

Member
Lets say I have a long list of variables. Lets say that some of these have been struck through manually, perhaps as to signify that these variables have been cancelled, or finished, or whatever.

If I have formulas at the end of the list that run calculations based on all the variables, is it possible to get those formulas to ignore values that have been struck through? The simplest thing would be to simply delete those values rather than strike through them, but they need to be kept in the list for reference.
 
Hi there,

I don't think there is a formula that can check the format of the text, but is maybe a helper column a possibility?
 
What do you mean? Are you saying that if I had an additional column with values that said 'cancelled' or 'closed' next to each strikethrough I could get a formula to ignore those values?
 
What sort of formula in particular would work there? Lets say A1:A100 has various values and column B1:B100 is mostly blank but occasionally contains the word "Cancelled". How would I sum the values in A1:A100 that don't have a "Cancelled" value in column B?
 
Hey PipBoy808,

Try this.

1) Place your cursor on cell C1 and go into the Define Name (Formulas -> Define Name/Name Manager)
2) Ener Name as "StrikeCheck" (of your choice if you want) and under "Refers to" enter this formula =Get.cell(23,!A1), Click OK
3) On Cell C1, enter =StrikeCheck, this should display the True, False for the values strikethrough.
4) Enter the same formula =StrikeCheck on Cells C1:C100
5) Use the Sumproduct (=SUMPRODUCT((A1:A100) *(C12:C100=FALSE))) to find the sum of the values which are Non-striken.

All the best!!!
 
Hi Sriram / forum, sorry for interupting, "=Get.cell(23,!A1), " this is awesome.

I never thought this could be solved by formula, this will help me also. :)
 
Hi Luke Sir, the link provided by you has very valuable information, this will help me a lot.

I have prepared a crude document for my reference about "Get Cell", thought should share with the forum.

P.S. - Moderators, please delete the post if necessary.
 

Attachments

  • GetCellInfo.xls
    74.5 KB · Views: 14
You must log in or register to reply here.
Back
Top