Avatar of wrt1mea
wrt1mea

asked on 

I need an updated excel 2007 formula

I am using the following formula:

=SUM(IFERROR(INDEX('[Report1.xls]Sheet1!$AX$14:$AX$1000,MATCH(B8,'[Report1.xls]Sheet1!$P$14:$P$1000,0)),0),(IFERROR(INDEX('[Report1.xls]Sheet2!$AX$14:$AX$1000,MATCH(B8,'[Report1.xls]Sheet2'!$P$14:$P$1000,0)),0)))

It works just like I need it to. However, the problem I am having on the production sheet is that since it references an accounting code and returns the percentage, there are some situations where that accounting code is used more than once and it returns a percentage value for the sum of the percentages. What would be a simple way to add to the formula IF the value returned is greater than 100% (or 1), it gets subtracted out?

For example: the above formula returns 188%. It should only be 88%

The above formula returns 200%. It should only be 100%
Microsoft ExcelMicrosoft ApplicationsMicrosoft Office

Avatar of undefined
Last Comment
nutsch
Avatar of nutsch
nutsch
Flag of United States of America image

Use MOD

=MOD(SUM(IFERROR(INDEX('[Report1.xls]Sheet1!$AX$14:$AX$1000,MATCH(B8,'[Report1.xls]Sheet1!$P$14:$P$1000,0)),0),(IFERROR(INDEX('[Report1.xls]Sheet2!$AX$14:$AX$1000,MATCH(B8,'[Report1.xls]Sheet2'!$P$14:$P$1000,0)),0))),1)
Avatar of wrt1mea
wrt1mea

ASKER

MOD doesnt work right becuase it will subtract 100% from 100% leaving me zero. I need it to only subtract if the number is greater than 100%
Avatar of nutsch
nutsch
Flag of United States of America image

then, extending it

=if(SUM(IFERROR(INDEX('[Report1.xls]Sheet1!$AX$14:$AX$1000,MATCH(B8,'[Report1.xls]Sheet1!$P$14:$P$1000,0)),0),(IFERROR(INDEX('[Report1.xls]Sheet2!$AX$14:$AX$1000,MATCH(B8,'[Report1.xls]Sheet2'!$P$14:$P$1000,0)),0)))=1,1,mod(SUM(IFERROR(INDEX('[Report1.xls]Sheet1!$AX$14:$AX$1000,MATCH(B8,'[Report1.xls]Sheet1!$P$14:$P$1000,0)),0),(IFERROR(INDEX('[Report1.xls]Sheet2!$AX$14:$AX$1000,MATCH(B8,'[Report1.xls]Sheet2'!$P$14:$P$1000,0)),0))),1))
Avatar of wrt1mea
wrt1mea

ASKER

OK, That works for anything over 100%, but for the few items that I have that are 200%, its returning a zero.
Avatar of wrt1mea
wrt1mea

ASKER

And it should be 100%
ASKER CERTIFIED SOLUTION
Avatar of nutsch
nutsch
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of wrt1mea
wrt1mea

ASKER

nutsch

Simply amazing. Thanks for the dedication and patience.

Look for more questions from me.
Avatar of nutsch
nutsch
Flag of United States of America image

Glad to help, thanks for the kind words.

Thomas
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo