simple way to do this in Excel?

[skim milk]

Let's say I have cells A2 through A1000 that all contain some numeric value. I have various formulas that reference this range (sum, average, etc.)

How can i make it so that certain cells are omitted in calculations?

I want to be able to select the cells that should be omitted without deleting the values. How can I mark cells to omit?

 

[EagleKeeper]

I expect that the cells that you want to usr have only numbers.

Clear the unwanted cells. Blank them out..

Was shown this last month.

If you have to have the data, then you need to create s duplicate worksheet that follows the above idea. Have the second sheet copy the needed data from the primary and do all your work against the second sheet for the output needed with ex ludrf cells.

In otherwords, sheet One has everything. Sheet two had a copy of needed data for other work.

 

[Binky]

You could use a column next to your numbers and mark them for inclusion with any character (e.g. X). They you could use many forumulas to do whatever you want with those marked numbers, such as sumif, averageif, or my favorite sumproduct.

If you have some logic that would describe the cells you want to omit, such as larger than 1000 or less than 10, etc., you could build a formula for that without the mark column.

 

[juiio]

Use a second column to mark which ones you want to exclude (or include, if that is easier), and then use Control-Shift-Enter functions.

Example:
Column A has the values you want to average. Column B has an X if you want to exclude that row. You want a formula to calculate the average of values in A that don't have an X in b. You would enter:

=AVERAGE(IF(B1:B100<>"X",A1:A100)), and then instead of just pushing enter, push control-shift-enter.

 

[Binky]

I find sumproduct to be a little easier to use and understand. This will do the same thing and its slightly easier to read, and you don't have to enter as an array formula (shift-enter).

To sum all marked entries =sumproduct((A1:A100)*(B1:B100="X"))
To average all marked entries =sumproduct((A1:A100)*(B1:B100="X"))/count(B1:B100)