Simple calculator average formula keeps changing

xangelicamarie

New Member
Joined
Jun 16, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I have a calculator made. It’s split into 3 sections.

1. Percentage first half (D4-D9)
2. Percentage second half (D10-D15)
3. Percentage over all (D4-D15)

In the percentage first half, any time you make a change in the percentage second half portion to add numbers, it changes to D4-D10, even though it’s locked cells and set as absolutes

Any idea on how to make it stop changing?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi. Locked cells have nothing to do with formulas. Thats for preventing the cell, either its static value or formula, but not the formula result, being changed when you protect the sheet. Absoluting cells has nothing to do with the formula. Thats for changing the action when you drag.

What are the formulas you are using and when you say make a change do you mean in the cell itself or one that it depends on?
 
Upvote 0
Welcome to the Board!

This is a bit confusing. The 3rd item you listed encompasses 1 and 2. So I am not really what this is supposed to be doing.
It is important for us to know what data and formulas you have in all those cells. Please post this, and walk us through an actual example using your sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
The calculator I made runs from November - October to keep track of stats as percentages.

there 4 different columns and the results are split into 3 different result sections. Percentage first half, percentage second half, and percentage over all.

percentage first half is calculated with =average(c4:c9)
Percentage second half is calculated with =average(c10:c15)
And over all is =average(c4:c15)

as soon as you input any information in the c10 box, it changes the percentage first half formula to =average(c4:c10).... which is why I tried to lock it and make it absolute.

this is happening in each of my columns.

im hoping that better explains it :)
 
Upvote 0
Here's a look at what was made - maybe seeing it in more context may help.
 

Attachments

  • Calculator image.JPG
    Calculator image.JPG
    145.7 KB · Views: 3
Upvote 0
as soon as you input any information in the c10 box, it changes the percentage first half formula to =average(c4:c10)
I cannot reproduce that behavior. It does not do that for me.
In what cell exactly is this average formula placed?

Are you just typing into cell C10, or are you first inserting any rows?
Are you sure that the data is not set up in a table?
Is there any VBA code in the sheet module?
 
Upvote 0
Excel doesnt behave like that. Could you explain your steps so we can try to replicate?
 
Upvote 0
You have showed us something with D10 filled in a the formula showing D4:D9. You said that was changing to D4:D10 once D10 was filled. Well C instead of D.
 
Upvote 0
I've been trying to get the XL2BB to work with no success. I used both the D column and the C columns because the something is happening in each.
I didn't think it behaved this way either but i cant seem to get it to stop and its doing the same thing for the other people who are using it.

Let me see if i can show you guys through pictures.

Step 1 picture shows the before
Then, all i did was click on the C10 cell to put in the 92.5% and then it changed on its own
 

Attachments

  • Step 1.JPG
    Step 1.JPG
    84.7 KB · Views: 3
  • Step 2.JPG
    Step 2.JPG
    71.6 KB · Views: 3
Upvote 0
You never answered my question whether there is any VBA code involved.

Would it be possible to update your workbook to a file sharing site and provide a link for us to download it and look at it ourselves?
If you do, just be sure to remove any sensititve data first.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top