Posts by CoachK

Re: SUMIFS and " > "

Thanks, Rory!

Aside from the edit you provided, there's something about "> $10,000" being used as the criteria1 argument that SUMIFS doesn't like. On my end, the formula will evaluate to 0.

Consider the following table:

[TABLE="width: 520"]

[tr]

[td][/td]

[td]

A

[/td]

[td]

B

[/td]

[td]

C

[/td]

[td]

D

[/td]

[/tr]

[tr]

[td]

1

[/td]

[td]

Claim Number

[/td]

[td]

Loss Date

[/td]

[td]

Claim Cost

[/td]

[td]

Strat

[/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

1

[/td]

[td]

7/25/2016

[/td]

[td]

$196.60

[/td]

[td]

$1 - $500

[/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

2

[/td]

[td]

9/4/2016

[/td]

[td]

$17,236.23

[/td]

[td]

> $10,000

[/td]

[/tr]

[tr]

[td]

4

[/td]

[td]

3

[/td]

[td]

9/17/2016

[/td]

[td]

$17,255.56

[/td]

[td]

> $10,000

[/td]

[/tr]

[tr]

[td]

5

[/td]

[td]

4

[/td]

[td]

10/16/2016

[/td]

[td]

$8,588.08

[/td]

[td]

$5,001 -$10,000

[/td]

[/tr]

[/TABLE]

Can SUMIFS be used so that I can find the total $ paid for claims "> $10,000"? I know this can be done by changing the criteria1 argument to ">"&10000 or using a pivot table, but honestly I'm hoping to find a different work-around other than those mentioned.

Thanks in advance!!

FYI the formula I'm using is: SUMIFS($C2:$C4,$D$2:$D4,D2)

Re: Excel Charts & VBA

Actually, what I want to be able to do is only show the two most-recent consecutive quarters. For example, if I import 4 quarters worth of data into the workbook, then the charts should only show Qtr3 and Qtr4 only.

I am putting together an excel bar chart that will show sales activity by quarters. The excel workbook is setup in such a way that all of the calculations are done in the absence of data. In other words, if I import only two quarters of data into the workbook, then only Qtr1 and Qtr2 will be calculated. Now, the issue is how do I get the chart to be just as dynamic? Currently, the chart is setup so that it will show 4 quarters along the horizontal axis even if there's nothing to show for Qtr3 and Qtr4 (to continue with the earlier example).

Can VBA be used in such a way that the chart will only display the quarters for which there is available data (without having to manually [re]select the data range)?

Thanks in advance for your help!

I am working with an Excel workbook in which each of the tabs have been formatted to look like your standard, garden variety, run-of-mill job application. One of the tabs serves as an "aggregated application" and shows the collective response to each interview questions. For example, question 1 reads "Did the applicant demonstrate ability to execute Action Plan?" and the formula used here concatenates all the responses for this question (using &) and each response is separate by "|".

The tabs in the workbook are partitioned by interviewer, and what I want to do is see the responses that are specific to a particular interviewer. For instance, if interviewer A has 5 tabs in the workbook, I want to be able to "click a button" (in the aggregated application) and see only those responses relevant to interviewer A. In other words, the collective results of interviewer A's 5 interviews would be visible. Is this possible?

Thanks in advance for the assistance provided to resolve this!!!!

I am working on an Excel chart for which the vertical (Y) axis and data labels were showing dollar values like $100,000, $200,000; $300,000; etc. I've since formatted the vertical axis so that the values are showing in thousands - $100; $200; $300; etc. The issue is that the data labels have followed suite.

Is it possible to have the data labels show as $100,000; $200,000; etc even though the vertical axis is showing "in thousands"?

Thanks!

Re: Arithmetic Operations on Single Cell Elements

Both options work sufficiently. Thanks a million for responding!!!!

I am trying to sum numbers that are in a single cell. For instance, in a single cell can appear the numbers 34 07 123 15 88 (all of the entries are separated by the space character and one digit numbers have a preceding 0).

Does excel have a built in function that can handle this? If not, can a UDF help with this? Lastly, I think the numbers are stored as text.

I'm working with a large data set that has been sorted by last name so that duplicate records are paired (i.e. one record is in row 72 and its duplicate is in row 73) and filtered so that the relevant data is showing. I recorded a marco that performs a Copy=>Paste Special=>Values=>Add routine to certain rows of my duplicate data set. For instance, if I have duplicate records in rows 72 and 73 of a spreadsheet, the (recorded) macro will copy the values from cells AI73:AW73 and then add them to the values in AI72:AW72. Here are my issues:

1. The spreadsheet contains for than 200 duplicate records that need to be added together, and the macro I'm using tackles these duplicates one set at a time.

2. Since I'm working in a filtered spreadsheet and my next set of duplicates are showing in rows 95 and 96, then 141 and 142, then 174 and 175... I'm not sure how to record the macro so that all the visible cells are handled in one pass. It's probably not possible.

Therein lies my need. I need a macro that can cycle through all (and only) the visible rows copying the values in cells AI:AW of row (i) and adding them to the values in AI:AW of row (i-1), but "if and only if" the last names (found in column D) of row (i) and (i-1) are a match.

Thanks in advance!

Re: NOT All Numbers Affected by Numbering Formatting

NBVC,

After "studying" your Conditional Formatting solution, I figured out want how it's supposed to work! While still inside of the conditional formatting dialogue box, click Format, Number Tab, etc....

Brilliant!!!

Re: NOT All Numbers Affected by Numbering Formatting

...but then I'll lose the thousands separator...

Re: NOT All Numbers Affected by Numbering Formatting

When using the custom formatting "#,##0.00;-#,##0.00;0;@" positive numbers like 245 will show as 245.00 and 3.4 will show as 3.40 - which is perect! But is it possible to have "round" integers (e.g. 245, 350, or 10,000) show without the floating zeroes?

Is it possible to record a macro that will move "the cursor" (active cell) to Column B of the currently active row? That is, if I'm currently in cell AA2100, the macro would place the cursor in cell B2100; or if I'm in cell AZ755, the macro moves the cursor to cell B755, etc.

VBA code for this problem would be helpful as well.

Thanks!

Re: Charts & Secondary Axis

Thanks a Million (or a Billion if you get a perfect bracket)....that did the trick!!!!

I'm trying to plot two bar graphs in the same plot - one graph will measure total income, the other will measure average income. Since "total" is much larger than "average", a secondary (vertical) axis will be used. However, it appears that the chart types have to be different otherwise the bars will be superimposed onto one another. Is there a way to work around this? I would like the bars to show side-by-side.

Thanks in advance.

Re: NOT All Numbers Affected by Numbering Formatting

Excellent!!!

I'm viewing some financial data that needs formatting. I select the appropriate cells and format the corresponding values accordingly:

Right click / Format Cells / Number -> Decimal places:2 -> (check) Use 1000 Separator(,)

It's easier to read the sums and averages (at the bottom-right of the excel window) when the above described formatting is used.

But is it possible, and easily do-able, to have the zeroes show as 0 and not 0.00?

Thanks in advance!

Re: Counting the number of tabs in a workbook

Awesome!!!!

Re: Counting the number of tabs in a workbook

NBVC:

In the event that the tabs have different names other than 1,2,3, etc, how then would I be able to get 1 into A1 of the first sheet, 2 into A1 of the second sheet,...., 13 into A1 of the thirteenth sheet?

Lastly, would you be willing to explain how the following works:

=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255)

Thanks a gizillion!!!

Re: Counting the number of tabs in a workbook

Works like charm! Thanks, NBVC!