Free Microsoft Excel 2013 Quick Reference

Calculating YTD Sales

I've attached a small portion of my spreadsheet. I'm trying to figure out how to add everything on the left (current month) to everything on the right (YTD Sales) while maintaining my formulas on the right. This is also something that I will be doing every month on the same spreadsheet, so the YTD Sales can't just point to the current month sales.

I've got hundreds of places I need to do this to on the full spreadsheet. So some kind method is a necessity.

Can anyone help me with this?


Post your answer or comment

comments powered by Disqus
Looking for a formula to calculate YTD score. I'm using this formula: =YTDPntsAvail(LEFT($A$1,4),M15,R15,X15,AC15,AH15,AN15,AS15,AY15,BD15,BI15,BO15,BT15,NOW()) But receive #REF! error back...

YTD Points Available should be calculated by adding points available for all months prior to current month. For example, if the BSC update is occurring in March, YTD Points Available = January Points Available + February Points Available.

The attached file is only a small part of the entire workbook!

Hope somebody can help me on this issue!

I'm trying to create a simple sales report. No VBA code, only excel formulas.
I'm stuck on trying to calculate the weekly sales. I want excel to be able to recognize the day of the week and know that the month started mid week.
Ex. If the 1st of the month started on a Wednesday, it adds all the sales from Wednesday to Saturday only and
if the month ends on a Tuesday, it will calculate the sales from Sunday to Tuesday only.
I want it done automatically.
I've included a zipped excel sheet example of the worksheet for a visual example.
Hopefully I've been clear & concise!

Need help on how to find a formula that will help me calculate projected
sales for 2001. Needing to find in quarters

Do you know a formula to automatically calculate YTD information off of a
date or month? (i.e. I don't want to have to change the formula every month,
I just want to change the month at the top of the spreadsheet and have the
numbers automatically calculate)
I know I can nest a bunch of 'If' statements, but is there an easier way?
Thanks.

Tracking Daily, Weekly and YTD sales

I've been tracking my company's daily sales for years in a manually formatted Excel table, but I was recently introduced to PivotTables. For the most part they seem great, but I'm having trouble setting up the PivotTable to track daily, weekly, and YTD sales. I've managed to setup the PivotTable to show my daily sales in columns (Mon., Tues., Wed., etc...) and then the automatic Total column adds up the daily sales to arrive at my sales for that week. Now, I would like to have one more column tacked onto the end of the chart that shows my YTD sales for each price point.

Any advice would be very welcome.

I've pasted an example of my raw data and ideal pivottable below.

thanks!

raw data sample:

w/e date day description # tix $ tix
9/30/2007 9/24/2007 Monday Full Price 100 $1000
9/30/2007 9/24/2007 Monday Premium 200 $4000
9/30/2007 9/25/2007 Tuesday Full Price 150 $1500
10/7/2007 10/1/2007 Monday Full Price 200 $2000

Ideal PivotTable:

we 10/7/2007

Monday Tuesday Wednesday Total Week Total YTD
Full Price
Premium
Total Total Total Total Total

I need to create a spreadsheet to calculate a sales bonus for one person. The parameters are as follows:

If sales >0<20000 then Bonus = 5% of total sales

If sales >20000<=400000 then Bonus = 5% of first 20000 and 10% of sales >20000

If sales >40000<=60000 then Bonus = 5% of first 20000, 10% of next 20000 and 15% of sales >40000

If sales >60000 then Bonus = 5% of first 20000, 10% of next 20000, 15% of next 20000 and 25% of sales >60000

Thanks for any advice.

A daily spreadsheet has current days sales in Col F. I want to calculate Month-to-Date sales in Col G. Example; Cell F4 contains current day sales of $50....How can I calculate MTD Sales I G4 if G4 of yesterday was $35?
Jay

Hi Everyone,

How can I set up a formula to calculate YTD % Forecast Variation? The Forecast Variation as you can see in the spreadsheet is calculated on a daily basis by Dividing adjusted demand by the Forecast

The spreadsheet contains lots of data and looks like this (Down and Scroll Right) and looks like this for over 200 cells down. I need to work out what the YTD % FORECAST VARIATION is??? Any Ideas????

Date NO GOHigh Bill Cons SME Big Mobiles PS SOcrediteons TotalTotal Inb Credit

OFFERED
NO GO High BillCons SME Big Mobiles PSTN SODCrediteCons TotalTotal Inb Credit

ANSWERED
NOGO High BillCons SME Big Mobiles PSTN Rec SODCredite TotalTotal Inb Credit

ADJUSTED DEMAND
NO GO HighSingle Cons SME Big Mobiles PSTN SOCrediteCons TotalTotal Inb Credit

FORECAST
NOGO High Single Consumer SMEBig Mobiles PSTN SOCrediteCons Total VarnTotal Inb Credit

FORECAST VARIATION
Mon02/07/2007131423574786733239532588151956612528056521,64025747125722744595693237272321144854411777855320,58424353127422994652705237952401146955112007955720,9012477189435154435899741972249152651813277323,9562773143%-35%5%-22%-10%7%-4%6%-10%8%-13%-11%Tue03/07/200799717603814553935282212131446612036043417,3672089398617503767545334652133129646111775841617,1172054698917533781547934842157130146311855942117,1922065071228553843748737832046143448612535920,4772395739%-39%-2%-27%-8%5%-9%-5%-5%-1%-16%-14%Wed04/07/200789619283630603933571911137248811845847117,5802086389019183611597832961853132346711525645017,3682054489219213617599633141870133847311625745617,43220640651

Hi
> I m facing problem with my worksheets which i have to calculate total
sales
> in particular month.
>
> sheet 1 have
>
> Date Sale Undr 4% CST Sale Undr 10 CST Sale Undr 4% RST
> DD/MM/YY
> 1.10.2005 10000
> 2.10.2005 250000
> 3.10.2005
> 150000
> 4.10.2005 1000
> 1.11.2005
> 2.11.2005
> 3.12.2005
> 4.12.2005
> 1.1.2005 etc
>
> Sheet 2 have
> Quarterly Return
>
> Month
> Sale Undr 4% CST Sale Undr 10 CST Sale Undr 4% RST
Dear Bob philip
thanks for response but it is not working. I think theres is date you assume
in text but it is in date format mm.dd.yy.
January
> February
> March
>
> In Sheet 2 i want to calculate Sale amount for particular month which is
in
> sheet 1
> How it can be
>
> Thanks and regard

Hi
> I m facing problem with my worksheets which i have to calculate total
sales
> in particular month.
>
> sheet 1 have
>
> Date Sale Undr 4% CST Sale Undr 10 CST Sale Undr 4% RST
> DD/MM/YY
> 1.10.2005 10000
> 2.10.2005 250000
> 3.10.2005
> 150000
> 4.10.2005 1000
> 1.11.2005
> 2.11.2005
> 3.12.2005
> 4.12.2005
> 1.1.2005 etc
>
> Sheet 2 have
> Quarterly Return
>
> Month
> Sale Undr 4% CST Sale Undr 10 CST Sale Undr 4% RST
Dear Bob philip
thanks for response but it is not working. I think theres is date you assume
in text but it is in date format mm.dd.yy.
January
> February
> March
>
> In Sheet 2 i want to calculate Sale amount for particular month which is
in
> sheet 1
> How it can be
>
> Thanks and regard

Hello All! I am in need of help to calculate two sales run rates using
formulas.

The formula I currently use for a monthly run rate is:
=SUM(MTD Sales/Today's day # in month * Total # Days in month)
I manually add up number of weekdays in month (minus holidays) & today's day
# in month.

Through browsing the board, I am using the following formula to figure
number of weekdays in date range minus holidays (K1:K40)

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(J2&":"&J3)),2)

I have a spreadsheet set up and two of the columns will have yes or no in
them, I want to calculate the sales tax that was included in the po......see
below......

PO Amt. Taxable material Taxes Included in PO Sales Tax Due
$900.94 Yes Yes
$68.64 (how do I
get this # with a formula)

thanks for your help!!!
Kel :-)

Does anyone know how to calculate a prior balance based on current balance and increase/decrease?

Example:

Current Sales = 334858
Current Sales Increase over Prior Period = 335.3%
Prior Period Sales = ?

I am trying to create an Excel formula that will allocation daily sales
results for technicians. Each technician has an individual daily sales goal.
More than 1 technician can work a job, and a job can take more than one day.
For example, I have a primary tech that has a $1,000 day sales goal and a
helper with a $500 day sales goal. They both work 8 hours on day one, and on
day 2, the primary works 8 hours and the helper works 4. I want to base their
daily sales on their proportion of each's daily sales goals and the number of
hours worked.
For example, if the total job were $6,000, on day one, the primary tech
would be credited with $1,500 in sales and the helper $1,000. They would both
be over by 50%. Day 2 is where I have problems. The helper only works 50% of
the time, so his Daily Sales Goal for that job would be 50% of his daily
total. (Assume a maximum of 8 hours/day). How do I create a worksheet in
Excel with Mon - Sun days, where I can list all the techs, the total hours
spent each day and calculate their appropriate sales for each day. Assume
only one job per calculation.

Hope someone can help!

Hi Everyone,

Can I request for your assistance please? I am developing a template which will help me automatically compute and compare "Year Ago" or previous year sales as you in put current month sales.

I have attached herewith the sheet I am working on, my formula is short of one function, it fails to compute if it skips a month of data. Please see attached and below logic.

Jan to Dec 2010 Sales = 12,000,000
Jan = 1,000,000
Feb = 1,000,000
Mar = 1,000,000
YTD = 3,000,000

Jan to Dec Sales 2011
Jan = 1,000,000
Feb = .00
Mar = 1,000,000
YTD = 2,000,000

Comparison 2010 vs. 2011
2010 = 3,000,000
/
2011 = 2,000,000

Performance = 67%

Thank you very much.

Best regards,

Francis

I am trying to determine how to write a formula that allows me to calculate
YTD earnings but I cannot figure how to get the year-to-date to work. I know
how to get the number of weeks from one date to another (for example
8/1/05-4/12/06 = 255 days or 36.43 weeks). My issue is that I want the
formula to count only the weeks YTD 1/1/06-4/12/06. I want the formula to
know that if my begin date is in a prior year to only look at the current
years weeks.

Any assistance would be appreciated. I cannot seem to find an easy solution.

if we want to calculate future value for the next month sales, which is more
suitable function we can use in Excel?As long as I concern, Forecast is the
usual function used. Is there other function beside Forecast and Trend? How
to integrate the data in chart form?

Hello All! I am in need of help to calculate two sales run rates using
formulas.

The formula I currently use for a monthly run rate is:
=SUM(MTD Sales/Today's day # in month * Total # Days in month)
I manually add up number of weekdays in month (minus holidays) & today's day
# in month.

Through browsing the board, I am using the following formula to figure
number of weekdays in date range minus holidays (K1:K40)

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(J2&":"&J3)),2)<6))-
COUNT(K1:K40)

(1) How do I calculate monthly run rate based on today's date?
(2) How do I calculate quarterly run rate based on today's date?

TIA for any suggestions :-)

I have an excel sheet where I have data for everyday sales. Please find the dummy sheet below.

Date Total Sales Expected sales
1st Nov 2000 2000
2nd Nov 2100
3rd Nov 2110

Here I have a formula for expected sales which is Total sales till now + (Total Expected sales for previous day/remaining number of woking days). Hence expected sales for 2nd nov is 2000+2000/20 = 2100, for 3rd nov its 2000+2100/19 = 2110(excluding decimals). Likewise I need to calculate the expected sales for the remaining number of working days of the month. I want to automate the formula with the help of a macro.
Please assist.

I am recording my Weekly Sales on a single worksheet and naming the sheet with the date of the first day of each week (eg 240105). The total daily sales are made up from three types of sale and are in the same range on each sheet (eg B6:B8).

I now want to calculate my average daily sales from a range of worksheets. Can anyone help me create a way of devising a formula where I do not have to enter the worksheet name and cell range 52 times such as this (=+AVERAGE('030105'!B6:B8,'100105'!B6:B8)) ?!!

I need the shortcut as I want to show average sales for each day of week for monthly periods with comparison to previous year which will be a huge amount of work. Any help appreciated.

Gary Williamson

Hi
> I m facing problem with my worksheets which i have to calculate total
sales
> in particular month.
>
> sheet 1 have
>
> Date Sale Undr 4% CST Sale Undr 10 CST Sale Undr 4% RST
> DD/MM/YY
> 1.10.2005 10000
> 2.10.2005 250000
> 3.10.2005
> 150000
> 4.10.2005 1000
> 1.11.2005
> 2.11.2005
> 3.12.2005
> 4.12.2005
> 1.1.2005 etc
>
> Sheet 2 have
> Quarterly Return
>
> Month
> Sale Undr 4% CST Sale Undr 10 CST Sale Undr 4% RST
Dear Bob philip
thanks for response but it is not working. I think theres is date you assume
in text but it is in date format mm.dd.yy.
January
> February
> March
>
> In Sheet 2 i want to calculate Sale amount for particular month which is
in
> sheet 1
> How it can be
>
> Thanks and regard

I need a formula to calculate the price I need to charge for my product in order to net a specific amount.

I want to be able to enter the amount I want to net where I know the percent that my charge card company is charging to process the transaction.

Example: I want to net $90.00 on the sale. What should I set the sale price at if the processing company is charging 3.5% plus .15 cents per transaction?

Any help would be greatly appreciated.

Anthony

Problem:

Columns A & B contain the full price of various items along with a matching category.
The letter "D" in the category name indicates a 20% discount on the full price for that item.
We want to create a formula that calculates the appropriate sales price for each item.
Solution:

Use the IF, ISERROR, and FIND functions in the following formula:
=IF(ISERROR(FIND("D",B2)),A2,80%*A2)

Hi
> I m facing problem with my worksheets which i have to calculate total
sales
> in particular month.
>
> sheet 1 have
>
> Date Sale Undr 4% CST Sale Undr 10 CST Sale Undr 4% RST
> DD/MM/YY
> 1.10.2005 10000
> 2.10.2005 250000
> 3.10.2005
> 150000
> 4.10.2005 1000
> 1.11.2005
> 2.11.2005
> 3.12.2005
> 4.12.2005
> 1.1.2005 etc
>
> Sheet 2 have
> Quarterly Return
>
> Month
> Sale Undr 4% CST Sale Undr 10 CST Sale Undr 4% RST
Dear Bob philip
thanks for response but it is not working. I think theres is date you assume
in text but it is in date format mm.dd.yy.
January
> February
> March
>
> In Sheet 2 i want to calculate Sale amount for particular month which is
in
> sheet 1
> How it can be
>
> Thanks and regard


No luck finding an answer? You could always try Google.