Testing with IF and SUM

  • Thread starter Thread starter Gigabyte
  • Start date Start date
G

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 ?
 
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.
Click to expand...

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
 
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.
Click to expand...

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.
 
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.
 
I use this :
=SUMPRODUCT((A4:AF4=1)*10+(A4:AF4=2)*4+(A4:AF4=3)*3)
This works perfect.
Click to expand...

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.
Click to expand...

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.
 
Back
Top