Home

Problem with a formula in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-840515%22%20slang%3D%22en-US%22%3EProblem%20with%20a%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-840515%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20wondering%20if%20someone%20could%20help%20me%20with%20the%20problem%20underneath.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20typed%20the%20following%20formula%3A%3CBR%20%2F%3E%3DSUM(I2%3AI1962)%2FSUM(I17638%3AI19598)%3C%2FP%3E%3CP%3E%3DSUM(I1962%3AI3921)%2FSUM(I17638%3AI19598)%3C%2FP%3E%3CP%3E%3DSUM(I3921%3AI5881)%2FSUM(I17638%3AI19598)%3C%2FP%3E%3CP%3E%3DSUM(I5881%3AI7840)%2FSUM(I17638%3AI19598)%3C%2FP%3E%3CP%3E%3DSUM(I7840%3AI9800)%2FSUM(I17638%3AI19598)%3C%2FP%3E%3CP%3E......%3C%2FP%3E%3CP%3E.....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20sheet%20it%20formulas%20give%20percentages%20and%20it%20looks%20like%20you%20can%20find%20in%20the%20attachment.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20way%20I%20actually%20wanted%20to%20work%20but%20I%20could%20not%20find%20the%20correct%20formula%20for%20is%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(%20(I(C19601))%20%3A%20(I(D19601))%20)%20%2F%20SUM(%20(I(C%2419610))%20%3A%20(I(D%2419610))%3C%2FP%3E%3CP%3E%3DSUM(%20(I(C19602))%20%3A%20(I(D19602))%20)%20%2F%20SUM(%20(I(C%2419610))%20%3A%20(I(D%2419610))%3C%2FP%3E%3CP%3E%3DSUM(%20(I(C19603))%20%3A%20(I(D19603))%20)%20%2F%20SUM(%20(I(C%2419610))%20%3A%20(I(D%2419610))%3C%2FP%3E%3CP%3E%3DSUM(%20(I(C19604))%20%3A%20(I(D19604))%20)%20%2F%20SUM(%20(I(C%2419610))%20%3A%20(I(D%2419610))%3C%2FP%3E%3CP%3E%3DSUM(%20(I(C19605))%20%3A%20(I(D19605))%20)%20%2F%20SUM(%20(I(C%2419610))%20%3A%20(I(D%2419610))%3C%2FP%3E%3CP%3E.......%3C%2FP%3E%3CP%3E.......%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20C196**%20would%20refer%20to%20the%20number%20that's%20in%20that%20box%20and%20apply%20it%20as%20the%20number%20that's%20after%20letter%20I%20which%20will%20refer%20to%20a%20box%20together.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20way%20I%20would%20have%20been%20able%20to%20do%20job%20way%20faster%20and%20not%20typing%20a%20lot.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anybody%20knows%20how%20to%20do%20this%2C%20all%20the%20help%20is%20more%20than%20welcome%2C%20thanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-840515%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-840659%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-840659%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F404553%22%20target%3D%22_blank%22%3E%40basboon1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20what%20you%20mean%3F%3C%2FP%3E%3CP%3E%3CFONT%3E%3DSUM(INDEX(I%3AI%2CC19603)%3AINDEX(I%3AI%2CD19603))%2FSUM(INDEX(I%3AI%2CC%2419610)%3AINDEX(I%3AI%2CD%2419610))%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-840728%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-840728%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(INDEX(I2%3AI19598%3BC19601)%3AINDEX(I2%3AI19598%3BD19601))%2F(SUM(INDEX(I2%3AI19598%3BC%2419610))%3AINDEX(I2%3AI19598%3BD%2419610))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20made%20this%20from%20it%20but%20I%20still%20get%20an%20error%2C%20the%20purpose%20is%20to%20use%20the%20number%20in%20the%20cells%20C19601%2C%20D19601%2C%20C19610%20and%20D19610%20to%20select%20an%20area%20in%20the%20database%20to%20sum%20up%20and%20get%20a%20grand%20total.%20In%20this%20way%20I%20want%20to%20divide%20the%20database%20in%20pieces%20per%2010%25%20from%20lowest%20to%20highest%2C%20after%20that%20I%20divide%20that%20through%20the%20top%2010%25%20to%20get%20a%20percentage.%20Both%20sums%20are%20already%20in%20de%20Code%20but%20there%20still%20is%20an%20area%20thats%20wrong.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20simple%20example%20to%20understand%20my%20issue%20is%20maybe%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20cell%20A5%20there%20is%20a%20code%20that%20has%20the%20outcome%205%3C%2FP%3E%3CP%3EIn%20cell%20A6%20there%20is%20a%20code%20that%20has%20the%20outcome%2050%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20thing%20I%20want%20is%20to%20generate%20a%20code%20that%20does%20%3DSUM(I5%3AI50)%3C%2FP%3E%3CP%3EI%20only%20want%20to%20do%20it%20in%20the%20way%20from%20%3DSUM(%20I(A5)%20%3A%20I(A6)%20)%2C%20this%20is%20only%20not%20the%20correct%20way%20and%20I%20have%20no%20idea%20how%20to%20do%20this%20correctly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-841651%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-841651%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F404553%22%20target%3D%22_blank%22%3E%40basboon1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20at%20least%20starts%20with%20working%20formulas.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20SUM(%20INDEX(%24I%3A%24I%2CA5)%20%3A%20INDEX(%24I%3A%24I%2CA6)%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3EThe%20comma%20or%20semi-colon%20separator%20is%20determined%20by%20your%20language%20localisation.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EI%20only%20use%20Names%20to%20reference%20data%20so%20a%20version%20that%20I%20would%20use%20looks%20more%20like%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20SUM(subList)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ewhere%20'sublist'%20refers%20to%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20INDEX(list%2C%20firstRow%20)%20%3A%20INDEX(list%2C%20finalRow%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EI%20assume%20you%20will%20be%20happier%20with%20the%20first%20version.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-843399%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843399%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3BI%20found%20to%20a%20way%20to%20implement%20the%20formulas%20correctly%2C%20thanks%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E
basboon1
New Contributor

I was wondering if someone could help me with the problem underneath.

 

I typed the following formula:
=SUM(I2:I1962)/SUM(I17638:I19598)

=SUM(I1962:I3921)/SUM(I17638:I19598)

=SUM(I3921:I5881)/SUM(I17638:I19598)

=SUM(I5881:I7840)/SUM(I17638:I19598)

=SUM(I7840:I9800)/SUM(I17638:I19598)

......

.....

 

In the sheet it formulas give percentages and it looks like you can find in the attachment.

 

The way I actually wanted to work but I could not find the correct formula for is as follows:

 

=SUM( (I(C19601)) : (I(D19601)) ) / SUM( (I(C$19610)) : (I(D$19610))

=SUM( (I(C19602)) : (I(D19602)) ) / SUM( (I(C$19610)) : (I(D$19610))

=SUM( (I(C19603)) : (I(D19603)) ) / SUM( (I(C$19610)) : (I(D$19610))

=SUM( (I(C19604)) : (I(D19604)) ) / SUM( (I(C$19610)) : (I(D$19610))

=SUM( (I(C19605)) : (I(D19605)) ) / SUM( (I(C$19610)) : (I(D$19610))

.......

.......

 

The C196** would refer to the number that's in that box and apply it as the number that's after letter I which will refer to a box together.

 

In this way I would have been able to do job way faster and not typing a lot.

 

If anybody knows how to do this, all the help is more than welcome, thanks in advance!

4 Replies

@basboon1 

Is this what you mean?

=SUM(INDEX(I:I,C19603):INDEX(I:I,D19603))/SUM(INDEX(I:I,C$19610):INDEX(I:I,D$19610))

@Peter Bartholomew 

 

=SUM(INDEX(I2:I19598;C19601):INDEX(I2:I19598;D19601))/(SUM(INDEX(I2:I19598;C$19610)):INDEX(I2:I19598;D$19610))

 

I made this from it but I still get an error, the purpose is to use the number in the cells C19601, D19601, C19610 and D19610 to select an area in the database to sum up and get a grand total. In this way I want to divide the database in pieces per 10% from lowest to highest, after that I divide that through the top 10% to get a percentage. Both sums are already in de Code but there still is an area thats wrong.

 

A simple example to understand my issue is maybe:

 

In cell A5 there is a code that has the outcome 5

In cell A6 there is a code that has the outcome 50

 

The thing I want is to generate a code that does =SUM(I5:I50)

I only want to do it in the way from =SUM( I(A5) : I(A6) ), this is only not the correct way and I have no idea how to do this correctly.

@basboon1 

This at least starts with working formulas.

= SUM( INDEX($I:$I,A5) : INDEX($I:$I,A6) )

The comma or semi-colon separator is determined by your language localisation.

 

I only use Names to reference data so a version that I would use looks more like

= SUM(subList)

where 'sublist' refers to

= INDEX(list, firstRow ) : INDEX(list, finalRow )

 

I assume you will be happier with the first version.

Highlighted

@Peter Bartholomew I found to a way to implement the formulas correctly, thanks for your help!

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies