MID function problem

**zoharsb** · 09-06-2016, 05:26 PM

Respected,
4
-202.15
6.9
-17.9
13
11MID.xlsx

-15.38 (2.33#-0.35)
Problem(1)
AVERAGE -0.35 should come -30~ but as mid is only considering first 3 , while A2(-202) has 4
Problem(2)
mid function IS USED AS ONLY RELEVANT NUMBERS FROM A STRING HAS TO BE CONSIDERED
BUT THIS CREATED PROBLEM WHEN THE NUMBER OF NUMERALS TO BE CONSIDERED ARE NOT ENOUGH AND HAS OTHER CHARACTER/SYMBOLS BEHIND THE SIGN(WHICH IS NORMAL)
In future the case can come where there may be character/symbols ahead of it and delimiter used is # , so if possible make that version also
So has to make formula such that only numerals that are at starting of value is considered(till 1 places after decimal, if there is decimal)

Zohar Batterywala

**FlameRetired** · 09-06-2016, 11:33 PM

Hello Zohar,

Welcome to the forum.

Try array-entering this in C7.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Does this solve the first part of your question?

**zoharsb** · 09-07-2016, 01:11 PM

Hi Flameretired,
Worked , But am not able to understand this at this moment. ALSO I have to use this formula in a big excel file where this calculation has to be modified for 1000+ calculations, so it will be helpful for me if you can PLEASE PLEASE explain.
THANKS
Zohar Batterywala

**FlameRetired** · 09-07-2016, 06:52 PM

I'll do my best.

I attempted to discern the criteria patterns.

I reasoned that numbers < 0 are a special case that only require the integer portion to be returned. So --LEFT(A1:A6,FIND(".",$A$1:$A$6)) only executes if < 0 returns TRUE.

The FIND function returns the character position of ".". LEFT returns that many left-most characters. The "--" before LEFT coerces the resulting text "numbers" to their numeric values.

Where numbers are > = 0 they remain unchanged.

All resulting values are "hidden" in an array. If you were to select just this part of the formula in the formula bar and press

the F9 function key IF(A1:A6<0,--LEFT(A1:A6,FIND(".",$A$1:$A$6)),A1:A6) you would see this array that is the combined result of that IF function {4;-202;6.9;-17;13;11}.

This array is passed to AVERAGE which returns -30.6833333333333. ROUND of course shortens that to -30.68.

If you are interested in an alternative formula you can skip the LEFT / FIND and use the TRUNC function. I had overlooked that option. I have almost never had cause to use it.

That shortened formula would be

Formula:

Please Login or Register  to view this content.

It is still array entered. TRUNC is the shorter way to return the left-of-decimal portion of negative numbers.

Also if you are interested there are two devices you can use to analyze formulas. One is the F9 key mentioned above and the other is an Excel feature called 'Evaluate formula'.

With one of the cells containing the formula active click on FORMULAS > Evaluate formula. A window appears. Click the EVALUATE button repeatedly and Excel will show

how it evaluates the formula step by step.

These two tools are excellent aids for dissecting formulas and self instruction. I recommend them whenever I can.

Did this help?

**zoharsb** · 09-08-2016, 01:50 AM

HI Flameretired,

THANK YOU VERY VERY MUCH for showing me the new thing in excel. THANK YOU.

It will take me some time to understand(a day or two).

Can you please please mention me the best place where I can read about the ARRAY FUNCTIONS and what should I find in Help of excel(mine is Office 2007 on win 8.1)

Can the ARRAY FORMULAS work when cells are apart and inbetween cells are not to be counted LIKE

All this is for understanding, (Mostly I am not so expert.)
values of A1, A4, A8, A12, B2, B5, B9, B13 of Sheet 1 has only to be considered.
and its answer needed in Another SHeet(sheet 2) at A6.

All the other formulas I will make from this example is averaging monthly(values 30 days apart-originally 90 rows apart as after every set of values taken one row is left blank and in one row daily values comparing to previous days values are calculated) and weekly(values 7 days apart-originally 21 rows apart) data AND so inbetween cells has not to be considered.

Also

Can you please make(if possible) the above formula in normal way.

Zohar Batterywala

THANK YOU AGAIN

**FlameRetired** · 09-08-2016, 10:24 AM

Can you please please mention me the best place where I can read about the ARRAY FUNCTIONS and what should I find in Help of excel(mine is Office 2007 on win 8.1)

It is best to get a foundational understanding of array fundamentals first. This is a good place to start.

http://xldynamic.com/source/xld.SUMPRODUCT.html

After that study the many examples that show up here on the forum, and of course apply F9 and Evaluate formula to them.

I'll have a look at the rest.

**FlameRetired** · 09-08-2016, 10:54 AM

Can the ARRAY FORMULAS work when cells are apart and inbetween cells are not to be counted LIKE

All this is for understanding, (Mostly I am not so expert.)
values of A1, A4, A8, A12, B2, B5, B9, B13 of Sheet 1 has only to be considered.
and its answer needed in Another SHeet(sheet 2) at A6.

All the other formulas I will make from this example is averaging monthly(values 30 days apart-originally 90 rows apart as after every set of values taken one row is left blank and in one row daily values comparing to previous days values are calculated) and weekly(values 7 days apart-originally 21 rows apart) data AND so inbetween cells has not to be considered.

zoharsb,

You will need to upload another example file of what you describe there.

Also

Can you please make(if possible) the above formula in normal way.

My first thought is ... yes, but it will likely require a helper column step. Those can be hidden for aesthetic purposes. I will work on that.

**FlameRetired** · 09-08-2016, 11:02 AM

Here is what I mean by helper column.

In D1 of below this formula filled down.

Formula:

Please Login or Register  to view this content.

Then in C7 the final formula.

Formula:

Please Login or Register  to view this content.

	A	B	C	D
1	4			4
2	-202.15			-202
3	6.9			6.9
4	-17.9			-17
5	13			13
6	11			11
7	-15.38 (2.33#-0.35)	2.33	-30.68

**zoharsb** · 09-08-2016, 12:23 PM

Respected Flameretired,
Has attached the edited worksheet as asked by you.

**FlameRetired** · 09-08-2016, 01:06 PM

zoharsb,

I am near completely lost. I only have one question to start with.

Most of the figures in column C are offset one row below their respective dates. Does this have to be this way?

Although I cannot discern the logic of the intended outputs (so far) I feel certain that this would be simpler if all figures were aligned with their dates.

Is that acceptable?

**zoharsb** · 09-08-2016, 03:02 PM

Sir,

The figures are calculated from daily volume (WEEKLY AND MONTHLY) and what is the difference they make weekly or monthly can help to decide future trends.
So it is that way.

At present I am at my workplace so do not have the original file from which this figures are taken.
I am attaching link of the original file that is 168 MB about 3 month ago but now is of 207 MB and will increase by more 4 MB this week as I will add data.
https://www.dropbox.com/s/521p0aq8qa...done.xlsx?dl=0

**zoharsb** · 09-17-2016, 10:27 PM

Respected,
Please have a look as its important
Zohar Batterywala

**FlameRetired** · 09-17-2016, 10:46 PM

zoharsb,

Many of us (including me) are reluctant to download from 3rd party file hosting sites.

If you upload directly to the forum with a much smaller example I will have another look at it. Presently I do not understand the latest upload post #9.

I can not see the logic in the expected outputs or the instructions. As stated earlier the sometimes offset rows sometimes not offset feature still confuses me.

Maybe someone else watching this thread will be able to help us both.

**zoharsb** · 09-18-2016, 11:26 AM

Respected Flameretired,
The format of the file in which this has to be added(to calculate) is:

Every day at end of day, the date of that day is added

say the date is 1-sep on A1

after that one line(ROW) is left blank(as other data that is added is of 2 lines(ROWS) and one line is needed for calculation)

a week can have 4 or 5 days(A1-A10 or A1 to A13) and so difference has to be maintained.
pictorially-
A1(DATA OF 1-SEP)
A2(blank)
A3(calculated data of 1-sep)
A4(DATA OF 2-SEP)
A5(blank)
A6(calculated data of 2-sep)
A7(DATA OF 3-SEP)
A8(blank)
A9(calculated data of 3-sep)
A10(DATA OF 4-SEP)
A11(blank)
A12(calculated data of 4-sep)
A13(DATA OF 5-SEP)
A14(blank)
A15(CALCULATED DATA OF WEEK)
Now the data of A3-
FOR WEEKLY-Now the data that is only on A3-A6-A9-A12 is only to be considered ( I will manage all that is not as per routine ---- like 4 days week, etc. etc.) .
THE DATA ON A1-A4-A7-A10(AND A13) HAS NOT TO BE CONSIDERED.
Hope I can make the things clear. If not you can message me anytime for anything(my email ID is---- zoharsb at gmail dot com)

Zohar Batterywala

**zoharsb** · 09-22-2016, 01:57 AM

Respected,
Any solution?
Zohar Batterywala

**FlameRetired** · 09-22-2016, 04:26 PM

Zohar,

No. I am not able to come up with solution. I have resorted to guessing and have found no way to arrive at the expected figures.

I am unable to help. I am sorry.

**zoharsb** · 09-23-2016, 02:15 AM

HI Flameretired,
In anyway THANK YOU for the Showing me the newthing(array functions)
Zohar Batterywala

**zoharsb** · 09-28-2016, 02:21 PM

Respected,
I am not much expert. So cant understand this all but may guide you to help me.
Has find two links by which my problem for MID function can be solved ----seems.

https://www.extendoffice.com/documen...string.html#a1
https://excelxor.com/2015/06/29/simu...-in-a-string/+

Zohar Batterywala

**zoharsb** · 09-29-2016, 01:35 AM

Respected,

Am posting again as in previous reply the link seems to be truncated and was not working.

I am not much expert. So cant understand this all but may guide you to help me.
Has find two links by which my problem for MID function can be solved ----seems.

https://www.extendoffice.com/documen...string.html#a1
https://excelxor.com/2015/06/29/simu...s-in-a-string/

Zohar Batterywala

**zoharsb** · 10-01-2016, 10:08 AM

Respected Sir,
Has again tried to reformat the request PLEASE PLEASE have a look.
ITs important

C63 in the attached file is the required result-it is

(1)% wise difference between last 2 days
(2)2Day average of the original value
(3)3Day average of the original value (data feed done-NOT calculated figure)
(4)weekly average of the original value (data feed done-NOT calculated figure)

All four results on in same cell connected by &

Sir, Please Please understand that I am not 24X7 online but am able to check mail at every 2-3 hours and so replied immediately as soon as you asked. PLEASE PLEASE help me to get the solution as this will solve many of my issues(relating to inflow from dam-to-reservoir, reservoir-to canal, and many,
there are 7 calculation in each line and there are such 4dams*13reservoirs*15canals=780* 360 days -280,800 lines,

If I have to change MID values in each, it will take me a month or so, and even that if done will not solve the problem as the persons who will be really using night not have required know-how.

PLEASE PLEASE

Zohar Batterywala

**zoharsb** · 10-07-2016, 01:08 PM

Respected ,
Please Please help.
Zohar Batterywala

**alansidman** · 10-17-2016, 03:39 PM

crossposted; http://www.ozgrid.com/forum/showthread.php?t=201014

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

**zoharsb** · 10-18-2016, 12:56 AM

Respected Sir ,
I have first posted on that forum but has not got any response till 7-8 days so I posted also here(but till now also has not got any solution PLEASE PLEASE this has to be implemented at more than 300000+ places for which I will do the needed changes but atleast once I should get the idea)
This is the link----
http://www.ozgrid.com/forum/showthread.php?t=201014

Zohar Batterywala

MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Re: MID function problem

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Problem With NPV function

[SOLVED] Sum function problem

Bookmarks

Bookmarks

Posting Permissions