Function usage and outcome clarification

[CharlieEcho561]

I am attempting to derive a number (cost) if eihter of the two adjacent
column cells have whole numbers. The cost factor is a constant 11. So I
tried forcing the IF funcion then a combination of IF and OR but once I
suspect I got the formatting correct all I could get were alpha characters in
the cost column. Would someone please direct me how to get the numeric
number in the cost colum by multiplying either or both of the cells in the
subsequent adjacent cells in the row?
Thanks

 

[T. Valko]

Can you post an example with the desired result?

 

[CharlieEcho561]

Sheet should look something like this
a b c
1 Cost Monday Tue
2 11 1
3 22 2
4 11 1
5 33 2 1

SO, for each quantity in column b or c I want to multiply either/or with 11
to create the cost that days activity.

Is this sufficient clarification?

Shed
Shed2

 

[CharlieEcho561]

Sorry the outcome did not properly simulate the desired form. I hope this
makes it more clear.

A B C
1 Cost Mon Tue
2 11 1
3 22 1 1
4 11 1
5 33 1 2
6 0

 

[T. Valko]

Try one of these entered in A2 then copied down as needed:

If you only have column B and C to deal with:

=11*(B2+C2)

If you have more than just 2 columns:

=11*SUM(B2:H2)

Where H2 represents the column for Sunday

 

[CharlieEcho561]

Thank you very much Biff. I am sorry I had to leave the discussion last
evening but I applied your suggestion this morning and it rendered exactly
what I desired. I feel rather shallow seeing that the formulation was so
rudimentary and I need not have gone through such elaborate means to
accomplish my desired result.
Since this is my first exposure into this posting for help method, I have
another problem with a budget sheet I am working on. The challenge is I
would like to have totals for subparts within the column but I do not want
the intermittent subtotals in the column to misrepresent the grand total for
expenses in the column? I have tried subtotaling but I seem top need to go
through the column and apply the subtotal cells into a long =sum()
formulation. Do you recommend my initiating a new posting into the discussion
arena or is there another recommended method for easily answering this delema?

 

[T. Valko]

Once again, can you post an example and the desired result?

 

[CharlieEcho561]

Thanks Biff and here is my shot at how it might be represented. The
worksheet is about 8 columns wide by about 450 rows long if that makes a
difference. I used a SUM funcrtion like this =sum(B5+B10) to include only
the subtotals in the columns.

Screws Cost
Item 2 _____10
Item 3 _____20
Item 4 _____30
Sub total _____60
Nails
Item 6 _____10
Item 7 _____20
Item 8 _____30
Sub total _____60
Total ____120

 

[T. Valko]

There are a few ways to do this and it all depends on the setup.

One way that requires a slightly different setup. List the item for each
sub-item like this:

Screws__Item__Cost
Screws__Item 2__10
Screws__Item 3 __20
Screws__Item 4__30
Nails__Item 6__10
Nails__Item 7__20
Nails__Item 8__30

Let's assume that data is in the range A1:C7
Select the range A1:C7
Goto the menu Data>Subtotals

Make these selections in the user form:
At each change in: Screws
Use function: Sum
Add subtotal to: Cost
OK out

 

[CharlieEcho561]

Thanks Biff! I tried this to a segment and it works for the subtotaling
woderfully and smooth. It gets a bit busy with all of the labels in column
A, looks rather duplicative. Unfortunately the items are standard
governmental lables and tough to change into some generic names for ease of
'EXCELing'. Also there are something like 60 different item names. Is there
another way to change this existing spreadsheet in this slick fashion and
still not do what might be considered as major overhall? Bare in mind the
rows are about 400 lines.

Thanks Again!

 

[T. Valko]

It depends on how your data is setup.

I'll post a small sample file that demonstrates another method later on
tonight. I'm getting ready to take a break!

 

[CharlieEcho561]

Thanks enjoy your brake!
--
Shed2

It depends on how your data is setup.

I'll post a small sample file that demonstrates another method later on
tonight. I'm getting ready to take a break!

 

[T. Valko]

Here's a small sample file that demonstrates 3 different setups. Each one
uses basically the same formula.

xSubtotals.xls 17kb

http://cjoint.com/?mgxUzSheMR

My personal preference would be to use Excel's built-in Subtotal feature.

 

[CharlieEcho561]

Thanks you very much Biff. Please accept my apology for forgetting to send
you off a response for all your efforts. You have been a tremendous help. I
agree with your recommendation for using the subtotal feature. It is
obviously less cumbersome and it will be less intrusive to the old
spreadsheet I have to work with.
Again thank you very much for sharing your expertise and for being available
and persistent with your guidance.
Ed

 

[T. Valko]

You're welcome. Thanks for the feedback!