Home

Help adding on to an iferror calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-830535%22%20slang%3D%22en-US%22%3EHelp%20adding%20on%20to%20an%20iferror%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-830535%22%20slang%3D%22en-US%22%3E%3CP%3EForgive%20my%20lack%20of%20Excel%20understanding%2C%20but%20I%20need%20help.%3C%2FP%3E%3CP%3EI%20have%20a%20client%20who%20wants%20to%20see%20the%20change%20in%20sales%20from%20one%20month%20to%20the%20next.%20Often%2C%20one%20month%20will%20be%20blank.%3C%2FP%3E%3CUL%3E%3CLI%3EThe%20client%20wants%20when%20there%20is%20a%20new%20month%20of%20sales%20to%20show%20%2B100%25.%3C%2FLI%3E%3CLI%3EWhen%20the%20new%20month%20has%20no%20sales%2C%20and%20the%20prior%20month%20did%2C%20to%20show%20-100%25.%3C%2FLI%3E%3CLI%3EThe%20iferror%20statement%20works%20to%20show%20the%20-100%25.%20However%20it%20returns%20a%200%20for%20when%20sales%20are%20recorded%2C%20instead%20of%20the%20%2B100%25%20I'll%20show%20it%20below%26nbsp%3B%3C%2FLI%3E%3C%2FUL%3E%3CP%3E(by%20the%20way%20is%20this%20the%20best%20way%20to%20do%20this%3F)%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E637.6%3C%2FTD%3E%3CTD%3E1565.5%3C%2FTD%3E%3CTD%3E-59%25%3C%2FTD%3E%3CTD%3E%3DIFERROR((A6-B6)%2FB6%2C0)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4896%3C%2FTD%3E%3CTD%3E666%3C%2FTD%3E%3CTD%3E635%25%3C%2FTD%3E%3CTD%3E%3DIFERROR((A7-B7)%2FB7%2C0)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1110%3C%2FTD%3E%3CTD%3E1125%3C%2FTD%3E%3CTD%3E-1%25%3C%2FTD%3E%3CTD%3E%3DIFERROR((A8-B8)%2FB8%2C0)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E382.5%3C%2FTD%3E%3CTD%3E675%3C%2FTD%3E%3CTD%3E-43%25%3C%2FTD%3E%3CTD%3E%3DIFERROR((A9-B9)%2FB9%2C0)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2318.4%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E0%25%3C%2FTD%3E%3CTD%3E%3DIFERROR((A10-B10)%2FB10%2C0)%3C%2FTD%3E%3CTD%3Eclient%20wants%20%2B100%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E0%25%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E2413%3C%2FTD%3E%3CTD%3E-100%25%3C%2FTD%3E%3CTD%3E%3DIFERROR((A12-B12)%2FB12%2C0)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-830535%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-830567%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20adding%20on%20to%20an%20iferror%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-830567%22%20slang%3D%22en-US%22%3E%3CP%3EKia%20ora%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399924%22%20target%3D%22_blank%22%3E%40debbienz%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Etry%20this%20in%20D6%20and%20copy%20down%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(AND(A6%26gt%3B0%2CB6%3D%22%22)%2C1%2CIFERROR((A6-B6)%2FB6%2C0))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20648px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F129308iC184398F15A07C52%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-08-30_14-05-15.png%22%20title%3D%222019-08-30_14-05-15.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-830582%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20adding%20on%20to%20an%20iferror%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-830582%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399924%22%20target%3D%22_blank%22%3E%40debbienz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EIf%20I%20understand%20properly%2C%20here%20is%20a%20solution%20for%20you%20with%20a%20sample%20file%3C%2FP%3E%3CP%3EI%20used%20this%20formula%20in%20Cell%20C1%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(%3CFONT%20color%3D%22%23FF0000%22%3EAND(A1%26lt%3B%26gt%3B%22%22%2CB1%3D%22%22)%3C%2FFONT%3E%2C1%2CIF(%3CFONT%20color%3D%22%23FF0000%22%3EAND(A1%3D%22%22%2CB1%3D%22%22)%3C%2FFONT%3E%2C0%2C%3CFONT%20color%3D%22%233366FF%22%3E(A1-B1)%2FB1)%3C%2FFONT%3E)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ethere%20are%203%20situation%3C%2FP%3E%3COL%3E%3CLI%3Eboth%20columns%20A%20%26amp%3B%20B%20have%20values%3C%2FLI%3E%3CLI%3EA%20has%20value%2C%20B%20doesn't%3C%2FLI%3E%3CLI%3EB%20has%20value%20%2C%20A%20doesn't%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20415px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F129310iAD459CC7EC549A75%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Percentage.png%22%20title%3D%22Percentage.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3BHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-830622%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20adding%20on%20to%20an%20iferror%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-830622%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETHANK%20YOU!!!!%20THANK%20YOU!!!!!%20THANK%20YOU!!!!!%26nbsp%3B%3C%2FP%3E%3CP%3EWorks%20a%20charm%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-830623%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20adding%20on%20to%20an%20iferror%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-830623%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENabil%26nbsp%3B%3C%2FP%3E%3CP%3ETHANK%20YOU!!!!%20THANK%20YOU!!!!!%20THANK%20YOU!!!!!%26nbsp%3B%3C%2FP%3E%3CP%3EWorks%20a%20charm%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-830728%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20adding%20on%20to%20an%20iferror%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-830728%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3BAnother%20question%20if%20you%20don't%20mind.%3C%2FP%3E%3CP%3EI've%20shared%20the%20link%20of%20the%20terrible%20excel%20sheet%20I've%20been%20given%20to%20fix%20up.%20The%20current%20excel%20sheet%20they%20have%20is%20over%20100%20column%20wide%20as%20you%20can%20see..%20They%20are%20comparing%20three%20different%20criteria%20(current%2F%20last%20income%3B%20current%20last%20no%23%20cards%20presented%20and%20current%2Flast%20commission%20paid.%20it's%20comparing%20one%20month%20to%20the%20same%20the%20prior%20year%20AND%20then%20adding%20on%20totals%20to%20the%20end.%3C%2FP%3E%3CP%3ESo%20as%20you%20can%20see%26nbsp%3B%20It's%20currently%20un-workable!!!!!%3C%2FP%3E%3CP%3EI%20realise%20I'm%20not%20skilled%20enough%20to%20come%20up%20with%20a%20good%20solution%20for%20them%20to%20make%20this%20an%20easier%20workable%20spreadsheet.%3C%2FP%3E%3CP%3EDoes%20anything%20stand%20out%20to%20you%20to%20change%3F%20If%20we%20separated%20years%20on%20different%20worksheet%20tabs%2C%20what%20would%20the%20formula%20you%20sent%20me%20be%20if%20instead%20ir%20was%20added%20onto%20a%203D%20cell%20reference%3F%3C%2FP%3E%3CP%3EOr%20can%20you%20think%20of%20a%20better%20solution%3F%3C%2FP%3E%3CP%3EHere's%20the%20dropbox%20link%20to%20the%20excel%20sheet%26nbsp%3B%3CA%20title%3D%22a%20cracker%20of%20an%20excel%20sheet%22%20href%3D%22https%3A%2F%2Fwww.dropbox.com%2Fs%2Fmdr6o746gp4amog%2Ftech%2520example.xlsx%3Fdl%3D0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.dropbox.com%2Fs%2Fmdr6o746gp4amog%2Ftech%2520example.xlsx%3Fdl%3D0%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-832927%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20adding%20on%20to%20an%20iferror%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-832927%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399924%22%20target%3D%22_blank%22%3E%40debbienz%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20problem%20here%20is%20that%20the%20data%20is%20being%20entered%20into%20a%20report%20format%2C%20which%20makes%20it%20very%2C%20very%20hard%20to%20analyse%20and%20focus%20on%20specific%20areas%20of%20the%20data.%20A%20better%20way%20to%20handle%20this%20is%20to%20create%20a%20flat%20data%20entry%20table%20with%20the%20following%20columns%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E-%20Activity%20type%20(i.e.%20Revenue%2C%20Card%20usage%2C%20Commission)%3C%2FP%3E%0A%3CP%3E-%20Company%20name%3C%2FP%3E%0A%3CP%3E-%20commission%20%25%3C%2FP%3E%0A%3CP%3E-%20Date%3C%2FP%3E%0A%3CP%3E-%20value%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20table%20will%20have%20a%20lot%20of%20rows%2C%20but%20just%20these%20four%20columns.%20With%20this%20flat%20table%20you%20can%20build%20a%20pivot%20table%20and%20calculate%20the%20differences.%20The%20pivot%20table%20can%20be%20made%20to%20look%20similar%20to%20your%20existing%20layout%2C%20or%20you%20can%20create%20different%20pivot%20tables%20to%20focus%20on%20different%20aspects%20of%20the%20data.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20pivot%20tables%2C%20the%20report%20can%20be%20filtered%20and%20show%20the%20insights%20that%20are%20relevant%20without%20scrolling%20dozens%20of%20pages.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20too%20complex%20to%20answer%20in%20a%20single%20question%2C%20but%20the%20data%20can%20be%20converted%20with%20Power%20Query%20(Get%20%26amp%3B%20Transform)%20on%20the%20Data%20ribbon%2C%20and%20you%20may%20want%20to%20catch%20a%20tutorial%20on%20pivot%20tables%20to%20get%20you%20started.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Forgive my lack of Excel understanding, but I need help.

I have a client who wants to see the change in sales from one month to the next. Often, one month will be blank.

  • The client wants when there is a new month of sales to show +100%.
  • When the new month has no sales, and the prior month did, to show -100%.
  • The iferror statement works to show the -100%. However it returns a 0 for when sales are recorded, instead of the +100% I'll show it below 

(by the way is this the best way to do this?)

637.61565.5-59%=IFERROR((A6-B6)/B6,0) 
4896666635%=IFERROR((A7-B7)/B7,0) 
11101125-1%=IFERROR((A8-B8)/B8,0) 
382.5675-43%=IFERROR((A9-B9)/B9,0) 
2318.4 0%=IFERROR((A10-B10)/B10,0)client wants +100
  0%  
 2413-100%=IFERROR((A12-B12)/B12,0) 
6 Replies
Highlighted

Kia ora @debbienz ,

 

try this in D6 and copy down:

 

=IF(AND(A6>0,B6=""),1,IFERROR((A6-B6)/B6,0))

 

2019-08-30_14-05-15.png

Highlighted

@debbienz 

Hi

If I understand properly, here is a solution for you with a sample file

I used this formula in Cell C1

=IF(AND(A1<>"",B1=""),1,IF(AND(A1="",B1=""),0,(A1-B1)/B1))

there are 3 situation

  1. both columns A & B have values
  2. A has value, B doesn't
  3. B has value , A doesn't

Percentage.png

 Hope that helps

Nabil Mourad

 

 

Highlighted

@Ingeborg Hawighorst 

THANK YOU!!!! THANK YOU!!!!! THANK YOU!!!!! 

Works a charm

Highlighted

@nabilmourad 

Nabil 

THANK YOU!!!! THANK YOU!!!!! THANK YOU!!!!! 

Works a charm

Highlighted

@Ingeborg Hawighorst Another question if you don't mind.

I've shared the link of the terrible excel sheet I've been given to fix up. The current excel sheet they have is over 100 column wide as you can see.. They are comparing three different criteria (current/ last income; current last no# cards presented and current/last commission paid. it's comparing one month to the same the prior year AND then adding on totals to the end.

So as you can see  It's currently un-workable!!!!!

I realise I'm not skilled enough to come up with a good solution for them to make this an easier workable spreadsheet.

Does anything stand out to you to change? If we separated years on different worksheet tabs, what would the formula you sent me be if instead ir was added onto a 3D cell reference?

Or can you think of a better solution?

Here's the dropbox link to the excel sheet https://www.dropbox.com/s/mdr6o746gp4amog/tech%20example.xlsx?dl=0 

Highlighted

Hello @debbienz ,

 

The problem here is that the data is being entered into a report format, which makes it very, very hard to analyse and focus on specific areas of the data. A better way to handle this is to create a flat data entry table with the following columns:

 

- Activity type (i.e. Revenue, Card usage, Commission)

- Company name

- commission %

- Date

- value

 

This table will have a lot of rows, but just these four columns. With this flat table you can build a pivot table and calculate the differences. The pivot table can be made to look similar to your existing layout, or you can create different pivot tables to focus on different aspects of the data.

 

With pivot tables, the report can be filtered and show the insights that are relevant without scrolling dozens of pages.

 

This is too complex to answer in a single question, but the data can be converted with Power Query (Get & Transform) on the Data ribbon, and you may want to catch a tutorial on pivot tables to get you started.

Related Conversations
Need some help figuring out edge://sync-internals/
HotCakeX in Discussions on
0 Replies
WVD On/Off based on activity
Adam Black in Windows Virtual Desktop on
1 Replies
Notifications from Planner in Teams
nwehl in Microsoft Teams on
0 Replies
Embedded Sharepoint List - Custom Forms not loading
sbayer in Microsoft Teams on
0 Replies
Adding Wiki Tab Issue
goatscandrum in Microsoft Teams on
1 Replies