Posts by darkyam

Re: Sum Array

Use named ranges and then simply =<named range of price>*<named range of week 1> and confirm with Ctrl+shift+enter.

Re: Rank Values From 2 Sheets

Nice work, Domenic.

Re: Move Control On Worksheet When Inserting Rows/Columns/Cells

bVal and lCap are simply names. Many programmers write the first letter of the type of the variable in the variable so they can tell what it is by looking at the name rather than having to go back to when it was first declared, so you have b(oolean)Val and l(ong)Cap. Boolean itself is just a logical value, always evaluating to either 1 or 0 (True and False, respectively).

Re: Rank Values From 2 Sheets

Sorry, all. When I tried it last night, I was lazy and put both of my lists on the same sheet, just different columns. The formula I posted works for that, but does indeed produce an error if they're on different sheets. I have no idea why it would work one way and not the other. Interestingly, if you evaluate both the ranges together by hitting F9, you get the #Value error, even if both ranges are on the same sheet, yet somehow it works if they're on the same sheet.

Re: Statical Report Of Table By Criteria

For mean, median, and mode, use the =AVERAGE(cells), =MEDIAN(cells), and =MODE(cells) formulas built into excel. If you're not familiar with pivot tables, you could also use array formulas and confirm with Ctrl+Shift+Enter.

Re: Rank Values From 2 Sheets

Put parentheses around the references in the formula, as in =RANK(B1,(Sheet1!B1:B3,Sheet2!B1:B3))

Re: Alphanumeric Management

There's probably a more efficient way to do this (especially with a Vlookup table), but these formulas should work. In A1, ="A0"&50&CHAR(47+ROW()). In A2, ="A"&TEXT(IF(RIGHT(A1,1)="Z",1,0)+MID(A1,2,3),"000")&IF(MOD(ROW(),36)=0,"Z",IF(47+MOD(ROW(),36)>=58,CHAR(64+MOD(ROW()-10,36)),CHAR(47+MOD(ROW(),36)))) and drag down. If your list is starting on a row other than 1, you will have to subtract 1 from every instance of both 47 and 64 in the formulas for every row below 1 (i.e., if you start on row 3, every 47 in the formula has to be changed to 45 and every 64 has to be changed to 62). HTH.

Re: Sequentially Count

Sorry, my mistake; formula should have been =IF(A2=A1,"",MAX($B$1:B1)+1)

Re: Sequentially Count

Assuming the entire list is sorted, you can do put a 1 in B1 (assuming data starts in A1), then in B2, =IF(A2=A1,"",MAX($A$1:A1)+1) and drag down.

Re: Exponentiation (Unary) With Negative Numbers

The reason Excel interprets 0-2^2 as -4 is because it is taking 2^2 first, which equals 4 and then subtracting it from 0. In your first two formulas, the exponentiation is considered before the sign. In other words, for the first one, it could be -(2^2) or -(-2^2). Since (-2)^2 = 4, putting a minus sign before that and then adding 4 equals 0. Richard is right; no real negative number squared equals a negative number.

Re: Associating Different Names With Numerical Values

What you want to accomplish is easily doable, but it would help if you changed the format of your sheet a little. For the options, instead of listing them, put them in different columns according to group (e.g., the four text plan options would be in one column and you could have a dropdown to select which you want). The formula for the total price then becomes a sum of the VLOOKUP of the plan price and VLOOKUPs for each option.

Re: Is Loan Amortization Affected By The Currency

Is this roughly what you're looking for?

Re: Chart Not Updating When Source Formulas Calculate

I'm not sure how this helps. Just as you wouldn't take a healthy person to a doctor to ask what is wrong with someone else, we can't fix a spreadsheet by seeing another one that has nothing wrong with it. My suggestion is that you start the stripping-down process again, checking the chart after each action, and see when it starts working normally. Otherwise, we will need a workbook that actually displays your issue.

Re: Cell To Cell Tabbing

Welcome to Ozgrid. Could you please save a workbook and post it here? Thanks.

Re: Use Vlookup &amp; Change Text In Result

=Substitute(SUBSTITUTE(SUBSTITUTE(VLOOKUP(A1,sheet2!$A$1:$W$17968,6),"/"," and "),"&","and"),"-"," ")

Re: Use Vlookup &amp; Change Text In Result

TheDude, that approach would only work on the base data. A find and replace won't do anything with values arrived at by formulas.

Re: Conditional Format With Multiple Values

=if(iserr(find("A",upper(a1))),if(iserr(find("B",upper(a1))),if(iserr(find("C",upper(a1))),0,1),1),1)

Re: Chart Not Updating As Source Data Formulas Calculate

Welcome to the forum. Could you please post an example?

Re: Use Vlookup &amp; Change Text In Result

=SUBSTITUTE(SUBSTITUTE(VLOOKUP(A1,sheet2!$A$1:$W$17968,6),"/"," and "),"&","and")

Re: Conditional Format With Multiple Values

Easiest way is to have 3 conditional format formulas, =FIND("A",A1), =FIND("B",A1), =FIND("C",A1). You don't need A*, as finding A will find all A*. Then just format them all to green. Doing it in one formula is a little trickier, as if it doesn't find all the letters, it will evaluate to an error, even with an OR formula, but it can be done if this doesn't work.