Excel Compound Formula

[jrobin5881]

REPOST- Put the original message in wrong forum by mistake. 8(

I'm looking to put a formula together in one cell versus many. I've tried using the "If" statement but its to messy.

I have an example of the table below. What I want to do is in cell A3 have a formula that looks to the right (Col B-E)and multiply by the number at the top of the respective colum when a number is found. Business rule - there can only be a single entry in col. B-E
NOTE: Row 2 are col labels

EXAMPLES:

Cell A3 would see a blank in col B & C and then multiply D3 by D1 and return 12

Cell A4 would see a blank in col B,C,D and then multiply E4 by E1 and return 2


A B C D E

1 188 38 12 1
2 Daily Week Month Year
3 1
4 2
5 2
6 2
7 1
8 1
9 3

 

[mintjulep]

Bizarre.

Something along the lines of:

=SUM(B$1*B3,C$1*C3,D$1*D3,E$1*E3)

will do what you are asking for, but it's not a very robust structure as there is nothing to enforce your "business rule"

 

[lionelhill]

You can do it with an array formula
Enter something like
=sum(B3:E3*B$1:E$1)
select it and use ctrl-shift-enter to make it an array formula
Then drag the corner of the box down the A column as far as you need.

 

[mintjulep]

or

=SUMPRODUCT(B$1:E$1,B3:E3)

 

[xlhelp]

also:
=INDEX($B$1:$E$1,0,MATCH(MAX(B3:E3),B3:E3,0))*MAX(B3:E3)

but I like the sumproduct one better

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[lionelhill]

even nicer! I always forget sumproduct.

And if you want to enforce the business rule a bit, you could at least add conditional formatting with a formula similar to:
=COUNT(B2:E2)>1 (obviously copied to all appropriate cells)
and highlight any results derived from places where two or more numbers have been entered. I prefer the highlight approach to merely finding the first number, because it makes less assumptions about what action is necessary if the business rule has been broken.

 

[jrobin5881]

You guy's are the best! Happy Holidays to you all. Thank you.