one cell in a row contains a number

[Chris]

I have a fixed row of 7 cells, any one of which might contain a number. the
cell with the number is the relevant one and it will determine my formula; i
will want to multiply the cell value by a number in another cel which is
chosen according to the number of the cell in the original row.

eg A1:A7- A3 contains 21
formula in A50 will wish to determine 21 x value of cell in column 3 of a
range of columns because the number 21 is in cell 3 in the row.

I was planning to use V or HLookup I think but cant fathom how to get the
cell number form the original row.

TIA Chris

 

[David Biddulph]

=SUMPRODUCT(ROW(A1:A7),A1:A7)

I notice that you are confused between rows and columns, Chris. A is a
column, not a row, and A3 is the 3rd row in that column.

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Rick Rothstein]

Sorry, but I am a little confused by your question. You start off by saying
you have a **row** seven cells, but then your example shows a **column** of
cells (A1:A7). Also, I am unclear if you just want the number that is in the
cell or if you want its column number or, perhaps, both?

 

[Chris]

Apologies to you all,

I mean that I have a row of 7 cells say N7:T7 for each use of the solution
it will progress down the sheet, N8:T8, etc.
I need the value in whichever cell has the number so that I can take it to
another formula and use it to find a value. like this
21
2
6
3
11 3
5
2
2
49 30

elsewhere in the workbook column N has a value, column M has a different
value and so on. My thinking was that if I could identify the column the
figure was in, i would be able to then tell my formula which value it had to
use to multiply with the value here and produce my result. i.e. 21 in cell
P7 means 21*2.2 = 46.2

I then have the added issue of if a row has more than one number but I was
trying to have some help and also to try and work things out for myself
rather than be lazy and simply ask for the whole answer. Sorry if I've done
wrong by that.

Chris

 

[Rick Rothstein]

I'm still a little unclear about your setup, but it appears that all you
need from any one row is the number that is in that row (as I understand it,
that in the seven cells within a single row, only one of those cells will
have a value and the rest will be empty). If that is the case, you can get
that value without knowing the row it is in. This formula will return the
value you are seeking (note that there are two minus signs in front of the
left parenthesis... they and the parentheses are needed to make this method
work).

=--(N7&O7&P7&Q7&R7&S7&T7)

You can either put this formula in a cell by itself and have your formula
use that cell for the value or you can use the expression (minus the equal
sign) directly within your formula and save yourself from having to use
another column, your choice.

 

[T. Valko]

I've read this thread about 10x and I'm still confused!

only one of those cells will have a value and the rest will be empty
=--(N7&O7&P7&Q7&R7&S7&T7)

If there is just one number in the range:

=SUM(N7:T7)
=MIN(N7:T7)
=MAX(N7:T7)

Seems they want to use the relative location of the number as the "index
number" for a lookup.

But, they're not telling us where to look for that index number.

.........N...O...P...Q...R...S...T
7........................1................

Ok, so the number 1 is found in Q7 and Q7 is the 4th cell relative to the
range.

Now what? How do we use 4 to look for the result you want?

 

[Rick Rothstein]

I'm not sure why I went with the concatenation method (after New Years
confusion maybe<g>); of course your three posted methods would work as well.
As for what the OP is trying to do... and how... your guess would be as good
as mine. As I said... "I'm still a little unclear about..."

 

[Don Guillett]

That's why I made my offer as I did. Don't like to guess...