How do I exclude a cell from a formula using macros

**megan145** · 09-08-2022, 10:22 AM

Hello, apologies if something similar has been asked before

I have a spreadsheet that we use in the lab for pooling samples based on concentration, sometimes the concentration is low enough that it skews the final pool and it needs to be removed from the calculations and just added manually... What I want to know is is there a way to use tickboxes (for example) to remove a sample from a formula, as often there are a number of samples that need removing and it is a pain to do this manually.

I've attached a basic template to show what I mean. The formulas in F98 to H98 are the ones I am talking about, I want to be able to remove a few samples from the calculation without going in and editing the formula every time.

Thanks in advanced

Meg

**oeldere** · 09-08-2022, 02:21 PM

Not a VBA solution.

Add the results in row 1 instead of in row 98.

See the attached file.

**megan145** · 09-09-2022, 04:26 AM

I don't know how to work forums lol I just need to remove this but not sure how

**megan145** · 09-09-2022, 04:33 AM

Hi,

Sorry I'm not sure that solves the problem. I would still have to manually change the formula in the cells in order to remove samples from the calculation.

What I want to avoid is having to change the formula to something messy like =SUM(H2:H17,H19:H38,H40:H42,H45:H48,H52:H62,H64:H76,H79:H92,H94:H97) which is what I have to do at the moment to remove samples from the calculation. Is there many an IF clause that would help me better than VBA?

Thanks

**Glenn Kennedy** · 09-09-2022, 04:37 AM

Change the formula in F2, to:
=IFERROR((147/D2)*C2,"")

to remove ugly errors.

Add an extra column (column I), x or anything, to exclude values, and sum using:

=SUMIF(I2:I97,"",F2:F97)

**megan145** · 09-09-2022, 04:46 AM

This is great, a much simpler solution to my problem than I thought I was looking for

Thank you!

**Glenn Kennedy** · 09-09-2022, 04:51 AM

You're welcome.

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

**bebo021999** · 09-09-2022, 05:01 AM

You need to input few mock up data, and point out the expected outcome. All formulas in column F:H display errors.

How do I exclude a cell from a formula using macros

LinkBack

Thread Tools

Rate This Thread

Display

How do I exclude a cell from a formula using macros

Re: How do I exclude a cell from a formula using macros

Re: How do I exclude a cell from a formula using macros

Re: How do I exclude a cell from a formula using macros

Re: How do I exclude a cell from a formula using macros

Re: How do I exclude a cell from a formula using macros

Re: How do I exclude a cell from a formula using macros

Re: How do I exclude a cell from a formula using macros

Thread Information

Users Browsing this Thread

Similar Threads

Exclude a cell from Large formula

[SOLVED] Difficulty adding value to exclude criteria in formula. Cell with formula returns blank.

Intelligent Macros (exclude values etc)

Formula to exclude a cell from being calculated, but still appear visually.

[SOLVED] Exclude macros from triggering msgbox

Exclude Blank Cell from Formula

Exclude cell from formula by formatting?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions