Max & Min Values

chandrashekar

Well-known Member
Joined
Jul 15, 2005
Messages
514
Office Version
  1. 365
Platform
  1. Windows
Hello,

Could you please share formula to fix Max and Min value.

Example1
values: 3.2, 7.3, 10.2, 12.9

Minimum = 3 - need formula where will I get 0
Maximum = 12 - need formula where will I get 20

Example2
values: 5.1, 9.3, 18.2, 12.9

Minimum = 5.1 - need formula where will I get 0
Maximum = 18.2 - need formula where will I get 20

Example2
values: 23.1, 24.2, 80.2, 97.9
Minimum = 23.1 - need formula where will I get 20
Maximum = 97.9 - need formula where will I get 100


Regards,

Chandrashekar B
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Best guess with no explanation of your logic being provided.
Excel Formula:
=MROUND(MIN(values),20)
 
Upvote 0
Best guess with no explanation of your logic being provided.
Excel Formula:
=MROUND(MIN(values),20)
Hello Jason,

Thanks it looks like working but for 9.3 formula should return 5(sorry was not clear in my first post).

I was working on chart where I need to fix Min & Max values for Vertical Axis(Y-Axis). I need to generate 3 chart in one sheet and am trying to write code to fix Min and max value using VBA.

Kindly suggest if you have more efficient way to work on it.
 
Upvote 0
Thanks it looks like working but for 9.3 formula should return 5(sorry was not clear in my first post).
I can not help further unless you explain why it should return the results that you are saying. I am not able to see any logical reason behind the results that you are expecting now that you have said 9.3 should return a result of 5.

Also, you said
Could you please share formula to fix Max and Min value.
which you have now changed to
code to fix Min and max value using VBA.
please make up your mind which you want.
 
Upvote 0
Hello Jason,

am trying to fix Y-Series(Attached image). Hope below points are clear

1. Bound Min = minimum value(rounddown to nearest value - Column B)
In this example min value is 42.05 I need a formula which can round this number to 40
2. Bound Max = maximum value(roundup to nearest value - Column B)
In this example min value is 48.02 I need a formula which can round this number to 50

Capture1.JPG
 
Upvote 0
That tells me no more than the original post.

All of the examples in post 1 are rounded to the nearest 20, which was what I based the formula on.

The examples in post 5 appear to be rounded to the nearest 10.

The example in post 3 has no logic at all.

A solution will not be possible without some kind of logical consistency.
 
Upvote 0
That tells me no more than the original post.

All of the examples in post 1 are rounded to the nearest 20, which was what I based the formula on.

The examples in post 5 appear to be rounded to the nearest 10.

The example in post 3 has no logic at all.

A solution will not be possible without some kind of logical consistency.
Hello Jason,

I can understand no logic behind it. I need to set y-axis to nearest min and maximum value so that graphs can fit to dashboard(as dashboard as many graphs).

Regards,

Chandrashekar B
 
Upvote 0
The best I can offer without some kind of logic.
Excel Formula:
=FLOOR(MIN(values),20)
Excel Formula:
=CEILING(MAX(values),20)
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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