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...
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)
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...
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
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.
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...
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