Replies

I’m not using the formats as my criteria. I’m using the column labels you showed in your previous post. If the column labels are not predictable, then my solution won’t work. The spreadsheet you posted has different column labels. Also, you seem to only want to include Me,You & Her. Why is Him excluded?

It would be pretty easy to write a custom function that would average all nonzero, percent-formatted numbers in a range. If you want to go that route, I can do that for you. But your last spreadsheet has confused me about the criteria for inclusion.

Try this:

=SUMIF(A1:H1,”Percent”,A2:H2)/SUMPRODUCT((A1:H1=”Percent”)*(A2:H20))

I was able to make this work as described in the newsletter, but I was confused as to how the function could work when used in a range name, but not when typed directly into a cell. If I type 4+5 into A1 and =evaluate(A1) into B1, I get a dialog box that says “That function is not valid”. Are there other functions that only work when used in a range name? Maybe I’m not familiar enough with Excel4. I didn’t start using Excel until version 5.

Create a separate data series for Actual and Goal.

Right click on the Goal column. Under “Format Data Series”, assign Goal to the secondary Y axis. I’ve also adjusted the width of the Goal column by adjusting the “Gap Width”.

Make sure your secondary y axis has the same scale as your primary y axis.

See attached example.

Create a separate data series for Actual and Goal.

Right click on the Goal column. Under “Format Data Series”, assign Goal to the secondary Y axis. I’ve also adjusted the width of the Goal column by adjusting the “Gap Width”.

Make sure your secondary y axis has the same scale as your primary y axis.

See attached example.

If your list starts in B2 and there are no embedded blank entries, you can try:

=average(offset(B2,count(B2:B100)-1,0,-min(5,count(B2:B100)),1))

If there are embedded blank entries, then you need to tell us how those should be considered in your average. Do you still want the last 5 days (even if there may be fewer than 5 data points), or do you want the last 5 observed data points?

If your list starts in B2 and there are no embedded blank entries, you can try:

=average(offset(B2,count(B2:B100)-1,0,-min(5,count(B2:B100)),1))

If there are embedded blank entries, then you need to tell us how those should be considered in your average. Do you still want the last 5 days (even if there may be fewer than 5 data points), or do you want the last 5 observed data points?

I logically solved it in about 2 minutes. But then, I’m a math geek.

Spoiler:
From the thousands column, you know that T=S+1. Based on that info, the ones column tells you that P=9. If P=9, then the tens column tells you that I is either 4 or 9. Since P=9, then I=4. The rest should be obvious.

I logically solved it in about 2 minutes. But then, I’m a math geek.

Spoiler:
From the thousands column, you know that T=S+1. Based on that info, the ones column tells you that P=9. If P=9, then the tens column tells you that I is either 4 or 9. Since P=9, then I=4. The rest should be obvious.

My example was purposely extreme, but yes, 4 is my answer. More typically, we add 20 dollar amounts but every displayed number is rounded to the nearest thousand or million dollars. The total does not always add up to the sum of the displayed numbers, but it’s more important that the total be correct. If anyone questions it, we just say it’s rounding and everyone accepts that. The alternative is to display the decimal points, but that leads to a more cluttered presentation which is usually deemed to be a worse solution.

My example was purposely extreme, but yes, 4 is my answer. More typically, we add 20 dollar amounts but every displayed number is rounded to the nearest thousand or million dollars. The total does not always add up to the sum of the displayed numbers, but it’s more important that the total be correct. If anyone questions it, we just say it’s rounding and everyone accepts that. The alternative is to display the decimal points, but that leads to a more cluttered presentation which is usually deemed to be a worse solution.

While Hans and Jan are right as always in their Excel advice, I question whether the original poster is really doing the right thing. I deal with these sorts of numbers issues all the time and we just let Excel do the calculation and display it without going through the contortions that Hans and Jan suggested. For example, if I want to add 1.5 and 2.5, adding them up gives me 4. If you add them up as displayed, you get 5. To me, 4 is the right answer.

While Hans and Jan are right as always in their Excel advice, I question whether the original poster is really doing the right thing. I deal with these sorts of numbers issues all the time and we just let Excel do the calculation and display it without going through the contortions that Hans and Jan suggested. For example, if I want to add 1.5 and 2.5, adding them up gives me 4. If you add them up as displayed, you get 5. To me, 4 is the right answer.

Bosco,
In order to break ties, add a small random number to each calculation, i.e. a1+b1+rand()/100000000

Bosco,
In order to break ties, add a small random number to each calculation, i.e. a1+b1+rand()/100000000