How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask ATLPROG Your Own Question

ATLPROG
ATLPROG, Computer Software Engineer
Category: Programming
Satisfied Customers: 7677
Experience:  MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
44910485
Type Your Programming Question Here...
ATLPROG is online now
A new question is answered every 9 seconds

I need excel calculated Formulas to capture my data, Excel

Customer Question

Hi, I need excel calculated Formulas to capture my data
JA: What programs and versions are you using?
Customer: Excel 2010
JA: What is your ideal outcome? How can we solve this for you?
Customer: PIVOT table
JA: Anything else you want the programmer to know before I connect you?
Customer: Need an expert in EXCEL MS EXCEL
Submitted: 18 days ago.
Category: Programming
Expert:  Kevin T replied 18 days ago.

How's it going.
Let me read of your question. Give me a minute.

Expert:  Kevin T replied 18 days ago.

Hi,

You'd like to create a pivot table. Is this correct?

Regards

Kevin

Customer: replied 18 days ago.
Customer: replied 18 days ago.
I already created
Customer: replied 18 days ago.
But I need calculated field
Expert:  Kevin T replied 18 days ago.

Ok can you explain your requirements and send me the file for a look.

Customer: replied 18 days ago.
1 min
Expert:  Kevin T replied 18 days ago.

No problem

Customer: replied 18 days ago.
Please see my data in the excel tabel, I need two calculated tabular columns in excel for following formal..Max of order in a day(includes orders in every hour) /total orders in a day
Max of order in an hour (includes orders in every min) /total orders in an hour
Customer: replied 18 days ago.
Any Update on this? Please
Expert:  Jason Jones replied 18 days ago.

Hello,
I am the person that will be helping you today.

It appears that the last expert has opted out. May I ask for you to describe what type of pivot table you are wanting from this document?

I will be standing by, looking forward to your response.

Thank you,
Jason

Customer: replied 18 days ago.
Thank You. I need two calculated fields in that pivot table..Max of order in a day(includes orders in every hour) /SUM orders in a day
Max of order in an hour (includes orders in every min) /SUM orders in an hour
Customer: replied 18 days ago.
Any update? is anyone looking into it
Expert:  Jason Jones replied 18 days ago.

I am looking into it. Please, give me a few examples of what the end result values should be.

Customer: replied 18 days ago.
Calculated fields should beMAX(Orders in a Day)/Sum of orders in a day
MAX(Orders in a min)/Sum of orders in a day
Expert:  Jason Jones replied 18 days ago.

What value are you referring to when you say, "MAX(Orders in a Day)"?

What value are you referring to when you say, "MAX(Orders in a Min"?

Customer: replied 18 days ago.
in my sheet there are orders in each min. right.I just need a formula in calulated filed for..=MAX(B3:Y3)/SUM(B3:Y3)
Customer: replied 18 days ago.
when I selected the date in slicer..it should shows the multiplier for specific date..which the formula is MAX(ORDERS in a day)/SUM(ORDERS in a day)
Customer: replied 18 days ago.
In the similar way..when I select hour in slicer..it should shows the multiplier for specific hour..which the formula is MAX(ORDERS in every min)/SUM(ORDERS in across the mins)
Customer: replied 18 days ago.
For exampleDate Multiplier
11/22/2015 11.54%
11/23/2015 10.05%
Expert:  Jason Jones replied 18 days ago.

Thank you.

Where did you get those values you just sent in? I will look at the sheet and make sense of it.

Customer: replied 18 days ago.
The similar way.for each hour multiplier in a day.Thanks..I am just learning
Expert:  Jason Jones replied 18 days ago.

Where did you get those values you just sent in? I will look at the sheet and make sense of it.

Customer: replied 18 days ago.
This is very simple but ..for me its not working out..and throwing error when tried to add calculated field. Just curious to know the logic behind that.
Expert:  Jason Jones replied 18 days ago.

I wish to help you understand this, but before I can do so, I need to fully understand what you are searching for.

You gave the following information:

Date Multiplier
11/22/2015 11.54%
11/23/2015 10.05%

"How" did you come up with those values?

Customer: replied 18 days ago.
Those are just sample..I need to get such thing in PIVOT calulated field
Customer: replied 18 days ago.
Did you understand the requirement ..or still you have questions..please let me know.If you can't please leave it. Thanks.
Expert:  Jason Jones replied 18 days ago.

Okay. Good day. Please, answer my questions so another will help.

Customer: replied 18 days ago.
Thank You..I just need the following.. when I select the date in slicer..it should shows the multiplier for specific date..which the formula is MAX(ORDERS in a day)/SUM(ORDERS in a day) in the similar way..when I select an hour..it should describe percent for every hour across the days..MAX(ORDERS in an hour)/SUM(ORDERS in an hour)
Customer: replied 18 days ago.
Those should show up in PIVOT calculated fields, that's it.
Customer: replied 17 days ago.
Hi Jason, Did you get any clue so far? thank You.
Expert:  Steve replied 17 days ago.

Hi there, my name is***** declined to answer this question. Do you still need help with it?

Customer: replied 17 days ago.
I still Need itIf you help me that would be gr8
Customer: replied 17 days ago.
Hi Steve..Can you please go trough the above comments..so that you will get an idea.
Expert:  Steve replied 17 days ago.

You can click here to do a secure upload of the spreadsheet to my dropbox. I'm the only one who will see it and I'll delete it when we're done. That will be the fastest way for me to help you if I can look at it.

Expert:  Steve replied 17 days ago.

Or is the spreadsheet posted above the one you're currently working with?

Customer: replied 17 days ago.
Yesh
Customer: replied 17 days ago.
Steve.
Expert:  Steve replied 17 days ago.

So you want the number in the pivot table to be max(orders)/sum(orders)?

Customer: replied 17 days ago.
I need below calculated files in the pivotDay Multiplier Hour Multiplier Minute multiplerDay Multiplier - Max(ORDER)/SUM of Orders in a day
Hour Multiplier - Max(ORDER)/SUM of Orders in an hour
Min Multiplier - Max(ORDER)/SUM of Orders in a min.
Customer: replied 17 days ago.
along with that in slicer if I select any date/hour/minute
same formula should run based on the selection.Is this possible.
Expert:  Steve replied 17 days ago.

"I need below calculated files in the pivot"? I'm not sure what that means. What files? I'm sorry, but maybe English isn't your first language and I'm having trouble understanding what you want.

Right now, the values in the pivot table are sum(orders) for a particular time period, depending on what you selected. My understanding is that you want those instead to be max(orders)/sum(orders) for the same time period. Is that correct?

Customer: replied 17 days ago.
Sorry Steve. Yes.In pivot there is a option called calculated field right..so I wanted to have this formula via calculated field.Again sorry for the inconvenience caused..
Expert:  Steve replied 17 days ago.

It's no problem, I just want to make sure I understand what you want. Hold on and let me do something and you can see if it's what you're after.

Customer: replied 17 days ago.
Thanks much Steve.
Customer: replied 17 days ago.
I did like this..=MAX($B3:$B25)/SUM($B3:$B25)
But the out put is not coming..and showing incorrect output
Customer: replied 17 days ago.
I have n number of orders in each hour and min across many dates..First I captured them in a pivot like as shown in the screen shot
Please find the screen shot for more details.
Expert:  Steve replied 17 days ago.

ok, i am looking at it now.

Customer: replied 17 days ago.
Thanks Steve.
Expert:  Steve replied 17 days ago.

In your screen print example,

For 11/26/2014, the max orders per hour for hour 1 is 27,
and the total orders per hour for hour 1 are 74.
You want to multiply all values in the pivot table for 11/26/2014 and Hour 1 by 27/74 = .36

For 11/26/2014, the max orders per day are 27
and the total orders per day are 97.
You want to multiply all values in the pivot table for 11/26/2014, entire day by, 27/97 = 28%

Is that right?

Customer: replied 17 days ago.
ExactlyBut same for hour peak order / orders in an hour
min peak order / orders in a min
Expert:  Steve replied 17 days ago.

hour peak order / orders in an hour ?

Customer: replied 17 days ago.
exactly
Expert:  Steve replied 17 days ago.

do you mean min(orders)/sum(orders) ?

Expert:  Steve replied 17 days ago.

I don't know what "hour peak order / orders in an hour" is

Customer: replied 17 days ago.
In Date wise - MAX(ORDERS)/SUM(ORDERS)
In Hour Wise - MAX(ORDERS)/SUM(ORDERS)
In Min Wise -MAX(ORDERS)/SUM(ORDERS)
Expert:  Steve replied 17 days ago.

Yes. For all three. but then you said min peak order / orders in an hour. Is that something additional (the "min")?

Customer: replied 17 days ago.
For example:See..today is 11/20 - assume over all order count in today is 30
I have orders like
0 hour -
1min - 4
2min - 5
3min -1
...
59min - 32 hour
3 hour
.
,
.
23 hour
Customer: replied 17 days ago.
I need to have the below dataDate wise
Hour wise
Min WiseSuppose if I select 20/11 ..then it shows..max order in an hour/sum of orders across the hours in a day (0, 1, ....23)
Suppose if I select any hour ...lets say I selected 2'nd hour across dates - out come should be ..max order in 2'nd hour across dates/sum of orders across all dates listed in table.
Suppose if I select any min ...lets say I selected 3'nd min across all dates - out come should be ..max order in 3'rd min across dates/sum of orders across all dates listed in table.
Customer: replied 17 days ago.
Hi Steve..you got my point. please go through the above comments, would be understand you better.Don't hesitate to ask me for any further questions. Thank you.
Expert:  Steve replied 17 days ago.

What version of Excel are you using? Do you have PowerPivot? (a free add-on from Microsoft for Excel pivot tables.)

Expert:  Steve replied 17 days ago.

I'm glad you put that explanation because you never said it was to be across all dates. That's something different, but might be something easier to do. Hold on.

Customer: replied 17 days ago.
My Version is 2010.Thank you so much.
Expert:  Steve replied 17 days ago.

I'm really sorry but I'm not sure about the best way to do this, and I've spent quite a bit of time on it already. If I were you, I would probably just calculate the max per day, max per hour and max per minute in a macro, add those to your table and do it that way. Sorry about that.

Customer: replied 17 days ago.
no problem..Thanks much for putting your effort. Thanks Steve
Expert:  Bruce Wilner replied 15 days ago.

For such ornate semantics, we use SQL, not Excel.