Testing with IF and SUM

[Gigabyte]

This is what am using for one field ( A4 ).
=IF(A4=1;10;"0"+IF(A4=2;4;"0"+IF(A4=3;3;"0")))

Now i want to calculate fields from A - AF but based on upper rules.
If user enters 1 output should be 10 if he enters 2 output = 4...

Example:
If A4 = 1, B4=1, C4 = 2 whole output should be 24.

How to make this ?

 

[Claus Busch]

Am Fri, 29 Oct 2010 13:03:22 +0200 schrieb Gigabyte:

This is what am using for one field ( A4 ).
=IF(A4=1;10;"0"+IF(A4=2;4;"0"+IF(A4=3;3;"0")))

Now i want to calculate fields from A - AF but based on upper rules.
If user enters 1 output should be 10 if he enters 2 output = 4...

Example:
If A4 = 1, B4=1, C4 = 2 whole output should be 24.

in A5:
=IF(A4=1,10,IF(A4=2,4,IF(A4=3,3,0)))
or
=IF(OR(A4<1,A4>3),0,CHOOSE(A4,10,4,3))
and fill to AF5
For Sum:
=COUNTIF(A4:AF4,1)*10+COUNTIF(A4:AF4,2)*4+COUNTIF(A4:AF4,3)*3


Regards
Claus Busch

 

[joeu2004]

This is what am using for one field ( A4 ).
=IF(A4=1;10;"0"+IF(A4=2;4;"0"+IF(A4=3;3;"0")))

Now i want to calculate fields from A - AF but based on upper rules.
If user enters 1 output should be 10 if he enters 2 output = 4...

Example:
If A4 = 1, B4=1, C4 = 2 whole output should be 24.

I think you want:

=SUM(IF(A4:AF4=1;10;IF(A4:AF4=2;4;IF(A4:AF4=3;3;0))))

entered as an array formula by pressing ctrl+shift+Enter instead of
Enter. In the Formula Bar, Excel will put curly braces around the
entire formula to identify it as an array formula, i.e. {=formula}.
You cannot type the curly braces yourself. If you make a mistake,
select the cell, press F2 and edit as needed, then press ctrl+shift
+Enter.

Alternatively, enter the following as a normal formula by simply
pressing Enter as usual:

=SUMPRODUCT((A4:AF4=1)*10+(A4:AF4=2)*4+(A4:AF4=3)*3)

Note: Learn not to put quotes around numbers that are intended to be
numeric, not text. That is, 0 instead of "0". Notice how it
simplified your formula. Also, if done improperly, it will cause
other problems in the future.

 

[joeu2004]

PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

 

[Gigabyte]

Thank you for reply guys.

I use this :
=SUMPRODUCT((A4:AF4=1)*10+(A4:AF4=2)*4+(A4:AF4=3)*3)
This works perfect.

Question:
If i put this formula into AG field, output will be in this field.
What do i need to do if i want to insert formula into field AH and if i
want to see the result in AG.

Basically, i want to put formula in one field but display the result in
other.

 

[joeu2004]

I use this :
=SUMPRODUCT((A4:AF4=1)*10+(A4:AF4=2)*4+(A4:AF4=3)*3)
This works perfect.

Thanks for the feedback.

Question:
If i put this formula into AG field, output will be in this field.
What do i need to do if i want to insert formula into field AH and if i
want to see the result in AG.

Basically, i want to put formula in one field but display the result in
other.

That is not how Excel works. If you put a formula into AG1 (e.g.),
you will see the result in AG1.

You can __also__ get the result in AH2 (e.g.) by simply entering the
formula =AG1.

And you can hide the result in AG1 either by hiding the column or row,
or by selecting a font color that is the same as the background color.

But why not simply put the formula into AH2 in the first place? What
is that you are really trying to accomplish?

If you want to display the formula that produces the result, there are
ways to accomplish that.

I would put the formula into AH2. Then in AG1 I would put the formula
=myFormula(AH2), where myFormula is a UDF (VBA code) like the
following:

Function myFormula(r As Range) As String
myFormula = r.Formula
End Function

To create the UDF, in the Excel window, press alt+F11 to open the VBA
window. In the VBA window, click Insert > Module to open the VBA
editing pane. Copy the text above and paste into the VBA editing
pane. You can now close the VBA window.