• 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.

about Ranges

rupana

New Member
Hello Members,

I have a requirement and I am not able to figure it out. using the formula Min(A1:A10) i am pulling out the minimum value / number from the range A1 to A10. Now, A1 to A10 have formulas. so instead of using Min(A1:A10), I want to use Min(formula of A1:formula of A10) to get the minimum value in that range. I am getting an error message #Value.
This I am trying so that I can eliminate the range A1:A10 physically and make use of the formulas. Hope I am clear on what I want. IF not I am truly sorry.
 
Hi rupana ,

I doubt that what you want can be done.

The formula of A1 is going to return the value in A1 ; similarly , the formulae in A2 , A3 ,..., A10 will return the values in those cells.

Thus , if you have a formula such as :

=MIN(formula of A1 : formula of A10)

the MIN function will have only two values to work on , not 10 values. Even then , the MIN function may not work , since the colon symbol ":" is used to define a range of cells , not values. Thus , you can have a formula such as :

=MIN( 55 : 77 )

but you can see that the formula returns 0 as the result , which is not what you want.

Even if the above formula were to return a valid value , it will not be the correct value , since it will not return the minimum of 10 values.

If you can explain why you wish to avoid specifying the range A1:A10 in the MIN formula , it may be possible to look at alternatives.

Narayan
 
@rupana

Further to what Narayan Sir wrote, if your formulas forming a range than it might be possible otherwise not as pointed out by Narayan Sir.

Regards,
 
Hello Members,

I have a requirement and I am not able to figure it out. using the formula Min(A1:A10) i am pulling out the minimum value / number from the range A1 to A10. Now, A1 to A10 have formulas. so instead of using Min(A1:A10), I want to use Min(formula of A1:formula of A10) to get the minimum value in that range. I am getting an error message #Value.
This I am trying so that I can eliminate the range A1:A10 physically and make use of the formulas. Hope I am clear on what I want. IF not I am truly sorry.
Sir, I have uploaded the screnshot as advised. Next, I want to eliminate the column B where in the =round(a1+.5,0) formulas exist and directly get the minimum value of the A column but by using the formula as mentioned above. this is to see that the number of cells used is less and also to get the calculations a little fast. Trust I could express my need correctly.
 

Attachments

  • Excel_Screen_Shot_23_8_14.png
    Excel_Screen_Shot_23_8_14.png
    103.5 KB · Views: 4
Sir, I have uploaded the screnshot as advised. Next, I want to eliminate the column B where in the =round(a1+.5,0) formulas exist and directly get the minimum value of the A column but by using the formula as mentioned above. this is to see that the number of cells used is less and also to get the calculations a little fast. Trust I could express my need correctly.
 
@rupana

A picture is worth a thousand words, and a movies is worth a thousand picture. But unfortunately this did not hold TRUE in many cases of Excel application.

But anyhow, this is what I understood: You want minimum of column B value, but you don't want column B calculation, you want it directly from Column A. If so then try below array formula.

=MIN(ROUND(A1:A10+0.5,0))

Enter with Ctrl+Shift+Enter, if this is what you not required than please write back.

Regards,
 
With following slight change in Somendra's formula CSE can be avoided.
=ROUND(MIN(A1:A10)+0.5,0)
The result should be the same.
 
Rupana

Please post files in future unless they contain confidential info
If they contain confidential info simply replace it with random values

Pictures don't give us much information at all
 
my actual problem is something else. I was trying to solve it and I got stuck at the above point. I will list out my original problem and seek a solution right away, instead of beating around the bush and wasting all of your precious time. Please see the data below:

Power to be generated - say: 400 watts
Solar Panels' Values[Watts] No of Panels
100 400 4
150 3
200 400 2
230 2
240 2
250 2
300 1
Now I can take 4 x100 or 2 x 200. Manually I will choose 2 x 200 W as we have to take only two panels as against 4 in case of 100 W. I could not figure it out in Excel, as I am less than a novice in Excel. once I input the total wattage [here 400] in a cell I should get the answer / value as 2 panels of 200 each in another cell[optimised solution in this example]. Please guide me and oblige. Thanks a Ton in advance to any one who puts me in right track.
Regards
 
Is it representing your complete range of solar panels? Please upload an Excel file as Hui has suggested.

And work out at least 4,5 cases manually (different total wattage and combinations you will use) so that the concept becomes clear.

Edit: Especially this part, if you have a requirement of 440 watts then a formula will probably suggest:
2 Panels of 230 each
but one can directly pick 1 panel of 200 and another of 240. If it is second type of optimization then it is quite challenging. If you're OK with One type of panel with minimum count then formula can be worked out fairly easily.
 
Last edited:
Sir - Sri. Shrivallabha - from your edit I would like to mention that only One type of panel values are to be choosen and we cannot mix panel values like your mentioning of 200 watts + 240 watts to get 440 watts. we have to choose 2 nos of 240 watts to get 440 watts [ in fact it will be 480 watts, but nearest value to 440 is 480 only in this case]. only One Panel value multiplied with so many number of panels but coming to the nearest value in question is to be choosen. That is, minimum total wattage nearest to wattage in question but again with minimum panels numbers is to be choosen. I am uploading a sample file with three senarios for better understanding of the forum. Hope I am able to give my thougths a shape and make a request of what I want correctly.
with regards to all of you.
Rupanagudi Ravi Shankar, Hyderabad
 

Attachments

  • solar panels selection procedure.xlsx
    15 KB · Views: 3
Rupana

I hope the above has answered your question.

In future please don't assume, give us the full picture and we will give you the best answer we can resolve.
There are often many ways to solve problems in Excel, and by giving us the big picture straight away we may give you a solution that tackles your problem from a totally different angle but still works.
 
In second case, can you please explain why we can't use 300 watts panel? It will also generate 600 watts (the same as 100,150,200) but will use lesser numbers of panels i.e. 2. Is it intended or you just missed it?

I am posting a file which uses the concept as I stated in the beginning. Minimum wattage generating * minimum panel.

I have used a named range. It is done through Formulas | Defined Names | Name Manager | New.
  • MinPanel refers to =INDEX(Sheet2!$F$7:$F$13,MATCH(MIN(CEILING(Sheet2!$F$2/Sheet2!$F$7:$F$13,1)^2*Sheet2!$F$7:$F$13),CEILING(Sheet2!$F$2/Sheet2!$F$7:$F$13,1)^2*Sheet2!$F$7:$F$13,0))
And then in cell F15 the following formula.
=CEILING(F2/MinPanel,1)&" panels of "&MinPanel&" is to be taken"

I am attaching the revised workbook. Please refer Sheet2.

If this does not meet your requirement then please post back and explain.
 

Attachments

  • solar panels selection procedure.xlsx
    15.8 KB · Views: 2
Sir. Sri. Shrivallabha. In the second case what you have pointed about the 300 W panels is 100% correct. I missed out the last entry. now actually it will be 2 panels of 300 watts as against 3 panels of 200 watts. Many thanks for your keen observation and correction.
As such what solution you proposed certainly seems to be what I want. Many thanks for solving my problem in a jiffy. I also have understood what Mr. Hui has commented. In future I shall try and follow his advice in seeking solutions.
Unless I get into a situation where I cannot get a solution with the method shown by you, I believe this thread can be closed.
with kind regards to you and Mr. Hui for all the time given for me and help rendered. I remain. Rupanagudi Ravi Shankar
 
Thank you Rupana for your feedback and kind words.

Defining a problem or requirement correctly and precisely is key process to getting solutions. Here's a good quote.
Charles Kettering the famed inventor and head of research for GM said:
A problem well-stated is half-solved.
 
Back
Top