IF/VLOOKUP & MOD Subtotal backwards math calculation

**pottie8** · 07-17-2022, 12:11 PM

Hi All

I was helped by the friendly people on this forum a while back to work on this sheet. I am now hoping for assistance on this next step - I really tried Google as much as I can, and appreciate the help on what I suspect to be relatively simple.

I need to be able to put items as a "once-off" and therefor mark column E as 0. This can be a once of in any specified amount of years (Column D).

Hoping the clever people on here can assist as per attached example

**oeldere** · 07-17-2022, 12:19 PM

You get better help if you add the expected result in your excel file.

Please also add the related cells.

**pottie8** · 07-18-2022, 04:08 AM

Please see attached updated with manual results .

refer to strawberries and banana as the examples.

Top current formula unable to change to zero and ignore all other results before and after as "blank".

Bottom is what the results should look like.

**AliGW** · 07-18-2022, 04:24 AM

There are no differences betseen the before and after tables ...

EDIT: Oh, I see - you should have highlighted the DIFFERENCES!!!

**pottie8** · 07-18-2022, 04:27 AM

Thanks for the edit - was doubting myself there for a second

**AliGW** · 07-18-2022, 04:35 AM

Try this copied across and down:

=IFERROR(IF(AND($E17=0,F$15="Year "&$D17),$C17*1.1,IF(COLUMNS($F17:F17)>=$D17,IF(MOD(COLUMNS($F17:F17)-$D17+$E17,$E17)=0,$C17*(1.1^(COLUMNS($F17:F17)-1)),""),"")),"")

Thanks for the edit - was doubting myself there for a second

Well, you could have been clearer, as I said!

**Glenn Kennedy** · 07-18-2022, 04:42 AM

or this (if it's different!!):

=IFERROR(IF(AND($E17=0,COLUMNS($F17:F17)=$D17),$C17*1.1^(COLUMNS($F17:F17)-1),IF(COLUMNS($F17:F17)>=$D17,IF(MOD(COLUMNS($F17:F17)-$D17+$E17,$E17)=0,$C17*(1.1^(COLUMNS($F17:F17)-1)),""),"")),"")

**pottie8** · 07-18-2022, 05:03 AM

Originally Posted by Glenn Kennedy

or this (if it's different!!):

=IFERROR(IF(AND($E17=0,COLUMNS($F17:F17)=$D17),$C17*1.1^(COLUMNS($F17:F17)-1),IF(COLUMNS($F17:F17)>=$D17,IF(MOD(COLUMNS($F17:F17)-$D17+$E17,$E17)=0,$C17*(1.1^(COLUMNS($F17:F17)-1)),""),"")),"")

THIS IS THE ONE! Thank you ever so much! Have a blessed day!

**nick.williams** · 07-18-2022, 05:05 AM

I had a slightly different approach, although see I am too slow

=IF($E17=0,IF(COLUMNS($F17:F17)=$D17,$C17*(1.1^(COLUMNS($F17:F17)-1)),""),IF(AND(COLUMNS($F17:F17)>=$D17,MOD(COLUMNS($F17:F17)-$D17+$E17,$E17)=0),$C17*(1.1^(COLUMNS($F17:F17)-1)),""))

**pottie8** · 07-18-2022, 05:05 AM

Thank you and sorry I wasnt clearer - this seems to have only considered the 1.1 increase once (no matter how many years was specified, but ALMOST it)

Glenn Kennedy solution worked the charm

Thank you so much for the time and effort!

**pottie8** · 07-18-2022, 05:06 AM

Thank you so much to all the responses on here - this is an amazing platform. THANK YOU!

**Glenn Kennedy** · 07-18-2022, 05:18 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 select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

**AliGW** · 07-18-2022, 06:21 AM

Thank you and sorry I wasnt clearer - this seems to have only considered the 1.1 increase once (no matter how many years was specified, but ALMOST it)

Well, you referred to it as a 'one-off' and I am pretty sure it resulted in what you said you wanted in the workbook, so yes, the lesson is to be 100% specific.

Glad to have helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

**pottie8** · 07-19-2022, 10:15 AM

Hi everyone

I'm building further on this and need another round of assistance to calculate subtotal values, but only if certain conditions are met. (Im assuming IF/Vlookup?)

attached example will better explain (I hope and trust) and the outcome should be the total of all "once-off" items (added together if it overlaps with another).

**AliGW** · 07-20-2022, 02:33 AM

I've copied your latest post here - for simplicity's sake, please contine here. I've removed the SOLVED tag and you can add some extra detail to the thread title if you wish.

**pottie8** · 07-20-2022, 02:35 AM

Thanks AliGW

Hi everyone

I'm building further on this and need another round of assistance to calculate subtotal values, but only if certain conditions are met. (Im assuming IF/Vlookup?)

attached example will better explain (I hope and trust) and the outcome should be the total of all "once-off" items (added together if it overlaps with another).

**pottie8** · 07-20-2022, 02:35 AM

Originally Posted by AliGW

I've copied your latest post here - for simplicity's sake, please contine here. I've removed the SOLVED tag and you can add some extra detail to the thread title if you wish.

Sorry I only read you copied it already now - im so sorry for the duplication :/

**AliGW** · 07-20-2022, 02:37 AM

Not to worry - that's why I asked the question in your other thread!

**soledad** · 07-20-2022, 04:01 AM

You can use the array formula in cell F28 then copy to right

Please Login or Register  to view this content.

Ctrl+Shift+Enter

**pottie8** · 07-20-2022, 04:20 AM

Thank you so much for this solution! !!

**soledad** · 07-20-2022, 04:22 AM

You're welcome. Good luck!

**Glenn Kennedy** · 07-20-2022, 06:11 AM

soledad... can you please update your profile, as it seems that you are no longer using Excel 2003 (IFERROR).

IF/VLOOKUP & MOD Subtotal backwards math calculation

Re: IF & MOD Columns to allow and consider "zero"

Re: IF & MOD Columns to allow and consider "zero"

Re: IF & MOD Columns to allow and consider "zero"

Re: IF & MOD Columns to allow and consider "zero"

Re: IF & MOD Columns to allow and consider "zero"

Re: IF & MOD Columns to allow and consider "zero"

Re: IF & MOD Columns to allow and consider "zero"

Re: IF & MOD Columns to allow and consider "zero"

Re: IF & MOD Columns to allow and consider "zero"

Re: IF & MOD Columns to allow and consider "zero"

Re: IF & MOD Columns to allow and consider "zero"

Re: IF & MOD Columns to allow and consider "zero"

IF & MOD Columns to allow and consider "zero"

Re: IF & MOD Columns to allow and consider "zero"

Re: IF & MOD Columns to allow and consider "zero"

Re: IF & MOD Columns to allow and consider "zero"

Re: IF & MOD Columns to allow and consider "zero"

Re: IF & MOD Columns to allow and consider "zero"

Re: IF & MOD Columns to allow and consider "zero"

Re: IF & MOD Columns to allow and consider "zero"

Re: IF/VLOOKUP & MOD Subtotal backwards math calculation

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula to count "low", "med", or "high" in 4 columns of data split by fiscal quarter

[SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"

[SOLVED] How to USE """"" cells count """"" change font color

[SOLVED] Problem creating Line Chart from columns "date" and "money"

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions