YouTubersLoveExcel#18: Logical Formula to Verify Patterns

Based on the attachment, I am looking for a formula in C1, C2 etc of sheet 2 to show up whether or not the Entries in A1 and B1 of Sheet 2 match up with entries anywhere in Sheet 1, then A2 and B2 match up with entries anywhere in Sheet 1 ...

If someone can help me, that would be great.

Rgds
okanem

I have some code in Excel 2007 and I want it to be compatable with 2003. How would I go about finding what the similar command is in 2003 which will create similar cell background colors to this Code:

      .ThemeColor = xlThemeColorLight2          ' only 2007
      .TintAndShade = 0.599993896298105      ' only 2007
And
    .Interior.ThemeColor = xlThemeColorAccent2
    .Interior.TintAndShade = 0.599993896298105

Is there a website that will give approximate matches?
Thanks in advance.

Someone recently created this formula for me to count the number of times "1 0 0 0 1" occurs in a row of data. I now want to edit it to count the following: "1 0 0 1", "1 0 1", "1 0 0 0 0 1", but I don't know how. I hoped it was a matter of just changing the pattern when it occurs in the formula, but that didn't produce the correct results. Can you help me understand this formula and how to modify it?

=SUM(LEN(B5&C5&D5&E5&F5&G5&H5&I5&J5&K5&L5&M5&N5&O5&P5&Q5&R5&S5&T5&U5&V5&W5)-LEN(SUBSTITUTE(B5&C5&D5&E5&F5&G5&H5&I5&J5&K5&L5&M5&N5&O5&P5&Q5&R5&S5&T5&U5&V5&W5,"10001","")))/LEN("10001")

Thanks in advance!

Hi All,

I have the below formula which I want to copy down to the next cells with a pattern on 3 so that

=SUM(D13,D49,D85,D121,D157,D193,D229,D265,D301,D337,D373,D409,D445,D481,D517)

becomes

=SUM(D16,D52,D88,D124,D160,D196,D232,D268,D304,D340,D376,D412,D448,D484,D520)

Is this possible, perhaps we need to use offset function?

Any help would be great appreciated.

Hello,

I am trying to put together sheet. That I can use to determine if a SD ticket is in/ or out of our SLA. I have found a few formulas but, have yet to find one that works for my situation.

I am basing the SLA off ITIL Priority levels for my organization.

1- 30 min
2- 1 hour
3- 2 hours
4- 4 hours
5- 8 hours
6- 24 hours
7- 3 days
8- 7 days
9- 14 days

In my sheet Column A has the date the Ticket was created.
Column B has the date the date closed.
Column C, Has the formula =B2-A2, to determine the amount of days to close.
Column D, Priority level of the ticket (1 High-9 Low)

I need to calculate column C against column D to see if the SLA was met or not. Output will be column E

Any help would be much appreciated.

Thanks!!!

I need a formula to monitor all formulas in a range to make sure they have not been blanked out or typed over.
Need formula in F1 to look at AB5:AE5002.
If formula has been blanked out or typed over, need F1 formula to return statement "Missing Formula".
Can this be done & how. Thanks, Odie Excel 2000.

I need a formula to verify all contents in a range are the same, resulting in a Yes or a No.
Formula to be located in cell M2
Looks at cells M5:M100.
If all cells have the same contents such as "PPG", Cell M2 says YES, if not says NO.
Can this be done? and How?
Excel 2000, Thanks Odie.

I have the following formula in my spreadsheet:

=IF(AND(H11:H50>=-1,H11:H50<=1),"Pass","Fail")

This is MEANT to read, H11 thru H50 are within -1 to 1, then Pass, otherwise Fail. However, the formula is not working as is and I am not sure where the error lies. I have tried different variations. Is the question I am asking even possible? I can do it the long way, but I figured there must be a shorthand method to arrive at the same answer.

Thanks,

Hi
I need help figuring out a formula. I want to use the If formula but want the value if true/false to be another formula, not just a set value.
Here's what I came up with (obviously wrong, please excuse my lack of experience)

=IF(D2>C2,"=(D2-C2)*5","=((60-C2)+D2)*5 ")

Thanks so much!

I need help on how I can set a formula that will provide for me a "Pass" or "Fail" value. Example: Cells D1:H1 will include values of 0, 1 or N/A. If any of the cells within the range of D1:H1 have a value of "0" then I need the to have the result of "Fail". If none of the cells have "0" then I need the to have the result of "Pass".

Thank you,

Brian