• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dashboard formula giving error

Ratan Bhushan

Ratan Bhushan

Member
Hi Team

I am preparing a dashboard where I am using formulas, when I am running the formulas, its giving error that @Excel RAN OUT OF RESOURCES
Please advise me what to do next so that I can achieve what I am looking for.
I am uploading the file.
I am using sumproduct formula where it is giving error again and again. and I have to use this formula to corrosponding cells also.
 

Attachments

  • PSTN DashBoard.xlsx
    81.7 KB · Views: 11
Hi Ratan,

If you are ref. to cells C18:C22 than there you have used full column W in in your formula, that may be a reason. See the file, I had converted your data to table and changed nomenclature in the formula, see if this works out.

Regards,
 

Attachments

  • PSTN DashBoard.xlsx
    83.4 KB · Views: 8
Somendra Misra said:
Hi Ratan,

If you are ref. to cells C18:C22 than there you have used full column W in in your formula, that may be a reason. See the file, I had converted your data to table and changed nomenclature in the formula, see if this works out.

Regards,
Click to expand...
@Somendra Misra-> and table coulmns may vary to the data entry below them as I am looking for the dynamic ranges.... so Please advise
 
Hi Ratan ,

Can you clarify why you have used these limits ?

=IFERROR(SUMPRODUCT((Qunit!$C$1:$I$1=Dash!$B$17)*(Qunit!$C$2:$I$1048576=Dash!B18)*(Qunit!$W$2:$W$1048576=Dash!$C$17)*(Qunit!$X$1:$AD$1=Dash!$B$17),(Qunit!$X$2:$AD$1048576))/SUMPRODUCT((Qunit!$C$1:$I$1=Dash!$B$17)*(Qunit!$C$2:$I$1048576=Dash!B18)*(Qunit!$W$2:$W$1048576=Dash!$C$17)*(Qunit!$X$1:$AD$1=Dash!$B$17)),"")

Just replace those limits with some more reasonable ones :

=IFERROR(SUMPRODUCT((Qunit!$C$1:$I$1=Dash!$B$17)*(Qunit!$C$2:$I$1000=Dash!B18)*(Qunit!$W$2:$W$1000=Dash!$C$17)*(Qunit!$X$1:$AD$1=Dash!$B$17),(Qunit!$X$2:$AD$1000))/SUMPRODUCT((Qunit!$C$1:$I$1=Dash!$B$17)*(Qunit!$C$2:$I$1000=Dash!B18)*(Qunit!$W$2:$W$1000=Dash!$C$17)*(Qunit!$X$1:$AD$1=Dash!$B$17)),"")

Use dynamic named ranges , to cater to future data expansion.

Narayan
 
See this file, compare the results.

Also note there are some enteries in range X:AD where you have mentioned - which is a text, replace all these with 0 and custom format to show as -.

Regards,
 

Attachments

  • PSTN DashBoard (2).xlsx
    83.6 KB · Views: 3
NARAYANK991 said:
Hi Ratan ,

Can you clarify why you have used these limits ?

=IFERROR(SUMPRODUCT((Qunit!$C$1:$I$1=Dash!$B$17)*(Qunit!$C$2:$I$1048576=Dash!B18)*(Qunit!$W$2:$W$1048576=Dash!$C$17)*(Qunit!$X$1:$AD$1=Dash!$B$17),(Qunit!$X$2:$AD$1048576))/SUMPRODUCT((Qunit!$C$1:$I$1=Dash!$B$17)*(Qunit!$C$2:$I$1048576=Dash!B18)*(Qunit!$W$2:$W$1048576=Dash!$C$17)*(Qunit!$X$1:$AD$1=Dash!$B$17)),"")

Just replace those limits with some more reasonable ones :

=IFERROR(SUMPRODUCT((Qunit!$C$1:$I$1=Dash!$B$17)*(Qunit!$C$2:$I$1000=Dash!B18)*(Qunit!$W$2:$W$1000=Dash!$C$17)*(Qunit!$X$1:$AD$1=Dash!$B$17),(Qunit!$X$2:$AD$1000))/SUMPRODUCT((Qunit!$C$1:$I$1=Dash!$B$17)*(Qunit!$C$2:$I$1000=Dash!B18)*(Qunit!$W$2:$W$1000=Dash!$C$17)*(Qunit!$X$1:$AD$1=Dash!$B$17)),"")

Use dynamic named ranges , to cater to future data expansion.

Narayan
Click to expand...
When I use dynamic ranges, the formula always show error... I have made dynamic ranegs if you check in range name tab.
 
Somendra Misra said:
See this file, compare the results.

Also note there are some enteries in range X:AD where you have mentioned - which is a text, replace all these with 0 and custom format to show as -.

Regards,
Click to expand...
Sir
Formula is working excellent, but when I change the quintile selection in front of error, it shows blank
 
Hi Ratan ,

You can use the OFFSET function to create dynamic named ranges.

An alternative is to use the INDEX version , which is on the following lines :

=Sheetname!CellReference : INDEX( ColumnReference , NumberofRows )

An example would be as follows :

=MASTER!$J$3 : INDEX( MASTER!$J:$J , COUNTA( MASTER!$J:$J ) + 2)

What this does is create a named range to refer to the range J3 through what ever is the last cell in column J to have data ; thus , if this last cell is J37 , the above named range will refer to the range J3:J37. As and when fresh data is added to rows 38 , 39 ,... in column J , this named range will extend to include this additional data.

The +2 is if the cells J1 and J2 are blank.

Narayan
 
NARAYANK991 said:
Hi Ratan ,

You can use the OFFSET function to create dynamic named ranges.

An alternative is to use the INDEX version , which is on the following lines :

=Sheetname!CellReference : INDEX( ColumnReference , NumberofRows )

An example would be as follows :

=MASTER!$J$3 : INDEX( $J:$J , COUNTA( $J:$J ) + 2)

What this does is create a named range to refer to the range J3 through what ever is the last cell in column J to have data ; thus , if this last cell is J37 , the above named range will refer to the range J3:J37. As and when fresh data is added to rows 38 , 39 ,... in column J , this named range will extend to include this additional data.

The +2 is if the cells J1 and J2 are blank.

Narayan
Click to expand...
I never knew this part... Thanks sir for teaching.
 
You must log in or register to reply here.
Back
Top