General discussion

Locked

Excel formula or help

By Whatme? ·
I have a list box in A43. they choose "NU" "NT"or "CA" at the beginning of each row
an ex of one row:
In cells h43:k43, the user will place an "x".
Only those boxes (h43:k43) with an x are totalled.
If they choose A43 (NU), then the values in h43=20,i43=25,j43=30,k43=40. The total will be in L43
If they choose A43(NT), then the values change in h43=12,i43=17,j43=22,k43=28
If they choose A43(CA), then the values change in h43=10,i43=14,j43=17,k43=20

Thanks for any help

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by Oz_Media In reply to Excel formula or help

So what is your question.

Collapse -

by Whatme? In reply to

Poster rated this answer.

Collapse -

by Whatme? In reply to Excel formula or help

Sorry.. the question is:
What formula would I use in cell L43 to get the total.
If A43=NU..then add the total of h43:k43 if there is an x in one of those boxes... see the values above

Collapse -

by Whatme? In reply to Excel formula or help

I just created tables for each region (NU,NT,CA)
in columns M, N, O, P, then in Q,R,S,T are totals if an "x" is placed in cells h43:k43

the formula which is probably quite wrong is:
=IF(B43="NU",(Q20:T30),"NT",(Q10:T10),"CA",(Q30:T30))

Collapse -

by FirstPeter In reply to Excel formula or help

If you can use another row for calculations, I think you'll find it to be MUCH less painful. Otherwise I think you'll end up with a gigantic IF statement that runs through each possibility.

If you can use row 44:

In cells H44:K44 plug in IF statements that determine the appropriate value if the "x" were selected (i.e., for NU it would be 20, 25, 30, and 40 in columns H, I, J, and K). You can then plug in an array in L43 to get the answer:

{=SUM(IF(H43:K43="x",H44:K44))}
<you can get the "{}" around the formula by editing it in the formula bar and pressing CTRL-ENTER, which tells Excel it's an array>

On the flip side, instead of using row 44 to just plug in the values based on cell A43 (i.e., 20, 25, 30, and 40), you could have it plug in zero if the cell doesn't have an "x", or the appropriate number if it does.

Collapse -

by Whatme? In reply to

Well, I worked through it. Couldn't figure out the array bit but I did use separate cells for my different tables(NU,NT, CA)then summed them up if there is an "x" in the range H43:k43and this is the formula that now works
=SUM(IF(B43="NU",CI43,(IF(B43="NT",CI9,(IF(B43="CA",CI68,0))))))

I am sure there is one formula to do it all but this is a great start.

Thanks
mtulloch@nulas.ca

Collapse -

by Whatme? In reply to Excel formula or help

This question was closed by the author

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums