# Excel formula

Discussion in 'Business Applications' started by mrindeed, Aug 3, 2005.

Not open for further replies.

Joined:
Aug 2, 2005
Messages:
49
Hi,

I am desgining a spreadsheet to calculate the cost of a process within our business.

This is how I would like the spreadsheet to function:
I have a list of functions on the left, in column B. Accross the top I have the processes that are possible for any scenario. Next to the function if the user enters a X then along the same row under the relevant processes another X is entered.

I have done this using a simple IF formula. e.g. =IF(C10="x","Required","") and just copied this formula into the cells that will need to X. (Hope that makes sense!)

Each process, even if it is needed for more than one function, will only need to be carried out once. I am looking to find a formula that will go in a cell beneath the rows of processes and if "Required" is entered anywhere in that column then a cost is entered at the bottom.

Basically a formula to detect whether anywhere in a column the word "Required" appears and enter a figure at the bottom for what the process will cost.

I have tried using formulas like =IF(C10:C20="Required","80","") - meaning if reuqired appears in any of the cells from c10 up to c20 then enter a cost of 80. Also using OR statements but both without much luck.

If anyone actually managed to follow this and suggest ant ideas id be very grateful.

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
3. ### XL Guru

Joined:
Aug 30, 2003
Messages:
2,702

For "if required appears in any of the cells from c10 up to c20 then enter a cost of 80", try:

=IF(COUNTIF(C10:C20,"Required")>0,80,"")

HTH,
Andy

4. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
I think he's trying to make a "picklist", Andy. I've given him an alternative not to list everything, but to only list those items required on the order. Hopefully, that'll suffice.

Joined:
Aug 30, 2003
Messages:
2,702

6. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Hmm, I had a different interpretation in mind. (Go figure, eh?! LOL!) What I'm thinking depends on your data structure. Could you zip/post a sample of your workbook?

Joined:
Aug 2, 2005
Messages:
49
Below is the formula i was looking for

The spreadsheet is essentially a pick list i guess. Plant changes are listed on the left. If one of these changes is to be carried out, you enter a x next to it (my manager likes this system, lol) and the relevant departmental activities (listed accross the top) for that change are signified by a Required being entered under them.

Then at the bottom, if a required appears in any of the column the process cost is calculated and then after that the man hours and total cost.

As Seen On