Search results

I always like when there is an Excel cartoon. A little levity for the day! http://www.dilbert.com/strips/

Thanks. Can the refernce be a named range?

Why can i not seem to get the hang of it :) the condition checks itself as True before comparing with data. =SUMPRODUCT((B7:B10)*((CELL("format",C7:C10)="C2")*1)) this gives me the sum of all data not the ones where the currency format = "C2" (us dollars) What is the fix to my formula...

Thanks :) Trying to keep format in the style that most organizations will not block. *.xls.

Thanks. Can UDFs be saved in a .xls format or are they in a .xlsm format?

Hi All, Luke M gave me a better and working formula to count quotes used in a range =SUMPRODUCT((ISNUMBER(FIND("""",A1:A4)))*ROW(A1:A4)) Is there anyway to make this look for all special characters below Char(32) without VBA? Basically looking to see and report if the data that a client...

Luke, how can you code the same SumProduct to look for Non-Printable Characters i.e. Code<=31 without having to put in all 31 finds? Can this be done in a SumProduct or does it need to be a VBA loop. (need to stay away from VBA for this sheet)

Thanks Luke! You are awesome. Yours is more elagant and it actually works. Thanks much!

I love sumproduct but have a hard time in implementation. For instance, am trying to find the row and column for any set of data that contains a special character like a single quote or double quote other invalid characters. If i put text in a1:a4 with a4 having a single quote in the text...

Thanks luke, that save me time from continuing to look for a solution. Think i will just delete the text part of the string and leave it as the value.

Hi all, I have a cell that has data validation set to be = a list of values like this: "01008, English as a Second Language" When a user picks a value, i want the Cell to only display 01008 with formats. I want to do this because the file will be saved as Tab Delimited which should only...

Simplest way You can create a helper column for all the criteria = Keep or Delete. Then highlight the helper column. Find Delete but click on Find All. Shift Click and Highlight all the items in the list that pops up below in the find all dialog box. Click Close. Then press Alt+E then...

I still get NA for all values However the formula i posted above seems to work fine. Would love a none array fomula, but this should work. =IF(ISERROR(A1:A3),NA(),IF((A1:A3)=MIN(IF(ISNUMBER(A1:A3),A1:A3)),A1:A3,NA())) entered as an array in cells B1:b3

oh yeah, forgot to say this should be entered as an array

Thanks for all your help. Think i got it: =IF(ISERROR(A1:A3),NA(),IF((A1:A3)=MIN(IF(ISNUMBER(A1:A3),A1:A3)),A1:A3,NA()))

Thanks Indian. I like where you are going with the formula, but that gives me: b1 = #n/a b2 = #n/a b3 = #n/a If i modify it as such: =IF(ISERROR(A1:A3),NA(),MIN(IF(ISNUMBER(A1:A3),A1:A3))) i get: b1 = #n/a b2 = 1 b3 = 1 Closer but would want b3=#n/a since a3 doesnot equal the min

=MIN(IF(ISNUMBER(A2:A4),A2:A4,NA())) still = 1 in all rows when i would like it to be b1 = #n/a b2 = 1 b3 = #n/a b4 = #n/a Since this will be used to highlight a chart data point for the min across the series at a specific time point.

Thanks luke. If i put that array in b1 and fill it across b1:b4, how can i get A1,A3 and A4 to =#N/A and have A2=that Min only?

By the way, Small and Large and Percentile also give the value of #n/a

Hi All, I have a dynamic chart range for 2 series. The chart range 1 has data that goes back 3 years. Chart range 2 only has data for the last year. I allow users to pick how far back they want to see the chart 1 month to 36 months. So if they choose more than a years worth of data i have...

Thanks Luke. Works like a charm. I hadn't thought about offset for this issue. Much appreciated!

Copy the range from excel (however you like) Go to powerpoint slide. On the Home Ribbon, select the arrow under PASTE and choose PASTE SPECIAL. On the left hand side of the Paste Special dialog box you will see PASTE or PASTE LINK. Choose Paste Link (this is what i mean by radio button)...

Also to Luke's point, You can also paste special and then choose Paste Link radio button on the left of the dialog which will allow your picture to change when the spreadsheet formatting is changed

Hi All, I have a chart that is based off of a table. In the table i have formulas that reference the row above to determine the starting position for this series. However, I would like the Users to have the ability to insert a row so that they can insert a series anywhere they want. I was...

Finally found a description with pictures on the web of what i was talking about doing: http://theclosetentrepreneur.com/how-to-add-a-vertical-line-to-an-excel-xy-chart