Variable equations?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i've got an equation like:

=IF(B63="A lot of electicial equipment",10*B8*B9,IF(B63="Some electicial
equipment",6.66*B8*B9,IF(B63="A little bit of electicial
equipment",3.33*B8*B9,IF(B63="No electicial equipment",0))))

i'd like to add another layer of complexity, but not sure how, with a
question like

how would you describe your efforts to reduce energy consumption
good
average
bad

where "good" mulitplies the previous answer by the coefficient of 0.6,
"average" x 0.8 and "bad" x 1

i've tried adding things like

=IF(B62="A lot of heating"&B65="Bad",25*B8*B9,IF(B62="A lot of
heating"&B65="Average",0.8*25*B8*B9,IF(B62="Some heating",15*B8*B9,IF(B62="A
little bit of heating",5*B8*B9,IF(B62="No heating",0))))))

thanks

shane
 
Something like this?

=IF(AND(B62="A lot of heating",B65="Bad"),25*B8*B9,IF(AND(B62="A lot
of heating",B65="Average"),0.8*25*B8*B9,IF(B62="Some heating",
15*B8*B9,IF(B62="A little bit of heating",5*B8*B9,IF(B62="No heating",
0)))))

Notice that in this example, an unspecified combination such as "A lot
of heating" and "Average" will return FALSE.
 
I think you need to bit a bit clearer with your question.
How is the new criteria to be incorporated?
best wishes
 
You will need to ensure that Good, Bad and Average are in the same cell. It
is easier to add the new formula into a new cell and just multiply the
results.

=IF(A11="Average",B11*0.8,IF(A11="Good",B11*0.6,B11))

I used a formula to give the results in A11 - this could be the number of
electrical appliances reduced in the household over the previous year are the
increase/reduction in the number of units used in the household electrical
bills over the period. If electric is used for heating, you should also
include average temperatures to the two periods to give a fairer result.

Hope this helps
Peter
 
Thanks iliace and billy
I think Bernard is right, i need to explains it more clearly....

the answer in one cell say (A3) has 12 variables that are fed from 2 cells
(A1 and A2) with drop down options

A1 =
A lot of heating = 5 (kwh)
Some heating = 10 (kwh)
A little bit of heating 5 (kwh)
No heating 0 (kwh)

each of the above may be multiplied by one of the following
A2
Good = *0.8
Average = *1
Bad = *1.2

the answer should be summed in the A1 cell rather than working out A1 and
then A2

thanks
shane
 
Shane

On sheet2 I created a couple of lists. A1:B4 =

A lot of heating 15 kwh
Some Heating 10 kwh
A little bit of heating 5 kwh
No heating 0 kwh

A7:b9 =

Average 1.0
Bad 1.2
Good 0.8

A1:A4 Named List1. A7:A9 named List2
A1:B4 named kwh. A7:b7 named Ratios

On sheet1 Data validation for A1 set at List1
A2 Data validation set for List2

Formula in A3 =
=VLOOKUP(A1,kwh,2)*VLOOKUP(A2,Ratios,2)

Regards
Peter
 
Back
Top