How to incorporate an if then else function to a sum formula

I have the formula

Please Login or Register  to view this content.

If zeros appear in any cell in column a I need the corresponding cell in column C to show a zero and the calculation to include the next cell in column A that has a value greater than zero.

I have attached a sample with a better description.

I thought this was a good opportunity for me to practice writing custom worksheet functions, so I created one for you called Last3() and installed it into your sample sheet. Here's the code:

Please Login or Register  to view this content.

How to install the UDF:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function works like this, from cell C5 in your sample workbook:
=LAST3($A$3:B5))

It's a two-column range at least 3 rows deep. You'll get a "Low Range" error if you put too few rows in the range.

I nested this into your "make sure column A isn't 0" and got this final formula for C5, copied all the way down the column:

=IF(A5=0, 0, LAST3($A$3:B5))

As it's already in VBA, here is a formula that works

Please Login or Register  to view this content.

Please note that this is an array formula and needs to be entered with CTRL-SHIFT-ENTER

I am also uploading up your spreadsheet so you can see the formula (In column D)

Hope that helps..
John

No, the UDF should be much quicker than array formulas. That's why I went that route. I imagined your project was large.

That is a sweet formula, though, Kraljb.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

Here you go, added a test at the end so you only get a value if the COUNTER is 3. With this change, you can now put the formula in C3 and copy down.

Please Login or Register  to view this content.

There's always something new to learn. I've only just started writing some custom functions, yours was the first I wrote for someone else...or maybe the second.

Anyway, always always use the forums. I've used them for years and am surprised every week by the new stuff I learn. Whole macros I used to use have been replaced by simple worksheet formulas. I recently wrote a complicated macro that someone else showed a way to do it with about 3 lines of code.

Hehe, I love it.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

If you have a followup question, be sure to post it here along with a relevant copy of your current workbook.