Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Multiplying by First Cell Over Zero

0

I have a long list of numbers, for which I am trying to find a formula of what the total would be, if the growth rate never fell below zero (i.e. for every cell representing negative growth, I replaced it with a zero).

The problem is the first cell with a number in the original list doesn't always start in the same column as the others. I need a way to detect the first number over zero, in order to start the formual after that.

Here is what I have so far:

1. Sheet 1: Core Data

2. Sheet 2: =IFERROR(IF((E3-D3)/D3>0,(E3-D3)/D3,0),0) --- this changes every negative growth between the Core Data to a zero

3. Sheet 3 =ROUNDUP(IF(D3>0,BB3*(1+AC3),0),0) --- This formula works to multiply the first data cell over 0 only if it's in the very first column of the original core data.

Is there a way to detect the first cell in the core data and start the formula to show growth in the corresponding third sheet?

Answer
Discuss

Discussion

Hi Wade and welcome to the Forum.   Please edit your question and use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing macro and data. Your question should then be easier to follow and we should be able to give specific help.
John_Ru (rep: 3647) Aug 1, '22 at 8:35 am
Don't need your long list of numbers, 10-30 should be enough. Need to be clear which cells your question refers to.
John_Ru (rep: 3647) Aug 1, '22 at 8:36 am
Thank you, John! I didn't even see that as an option. 
I've uploaded. The section in pink is where I am struggling, though I'm willing to start fresh if needed.
Basically, I want to show what the totals would be if there were no period of decline (i.e. zero growth instead of negative), but since each element starts at a different data, I can't figure out a way to insert the formula in the right cell (after the initial value over 0). 
I have about 14000 cells in total, so if there is way to indicate which corresponding cell to use, it would save me tons of time.
WadeBP (rep: 2) Aug 1, '22 at 11:32 am
Add to Discussion

Answers

0
Selected Answer

Wade

I could tell you how to determine the first non-zero figure in a row but I don't think that's needed here since I've taken a different approach in the attached revised sheet.

I've hidden your columns AA:CX and made column CY the same as B (so the 1790 figure, 0 or otherwise) e.g. cell CY1 has formula:

=B1

and the same down the column (6 or however many rows).

Given you want to increase any calculated population by any positive percentage  increase in a decade (or not increase it is the raw data value fell), in CZ2, the revised formula used nexted IFs and is:

=IF(B2<=0,C2,IF(C2>B2,CY2*C2/B2,CY2))

so it increases the numerical value figure in the column to the left by any positive percentage increase in population (in the raw data) or gives the raw data if it's a(/the) first non-zero value.. If the 1800 figure is less than the 1790 one, it doesn't change the value (it stays as per the previous 1790 column). This is then copied down and across, over the decades.

You'll see that the formula produces values in green cells CY3:DV3 which match your B9:Y9 sample values (but adds the original non-zero population)

You'll see that I've added an additional formatting rule so any decade-on-decade value drops appear yellow in the raw data and in the calculated data show that the value was held at the previous figure.

Hope this is what you need. Kindly mark this answer as Selected if so.

Discuss

Discussion

Thanks so much, John, but I need the results from a percentage increase, not a numeric increase. I added a new file to illustrate what I'm looking for. I want the results from the green row, but I'm looking for a way to detect the first number over 0 in each individual row. Otherwise, I just get a bunch of error results.
WadeBP (rep: 2) Aug 1, '22 at 1:23 pm
Wade, please see my revised Answer and new file.
John_Ru (rep: 3647) Aug 1, '22 at 3:11 pm
Thanks, John! This is exactly what I was looking for!
WadeBP (rep: 2) Aug 1, '22 at 6:43 pm
Great, glad it worked and thanks for selecting my answer 
John_Ru (rep: 3647) Aug 1, '22 at 6:45 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login