Microsoft Office Forums I cant get my Sum(Offset formula to pull appropriate data.
 Register FAQ Search Today's Posts Mark Forums Read

#1
07-28-2016, 08:01 AM
 Erickd90 Windows 10 Office 2016 Novice Join Date: Jul 2016 Posts: 4
I cant get my Sum(Offset formula to pull appropriate data.

I am starting a business and created a Breakeven to show my investors and partners what our numbers would look like depending on the number of sales reps we started out with.

Currently this is the formula I am using....

=+SUM(OFFSET(Sales!B\$19,0,0,1+\$F\$4,1))

The goal: When cell F4 is showing 0 or empty the cell will display the value listed in Sales! B19. However if My investor wants to add 4 sales reps, they will change cell F4 to 4 and that will then multiply Sales! B19 by 4 and display that number.

Now whenever I change the number from 0 in cell f4 it just multiplys by 13....
I cannot get it not to and i dont know why.

Does this make sense?

I have attached the file so that someone can help if needed.

Goal: Get Sales Rep Payroll (Per Month) to Scale according to the number in cell F4
Attached Files
 Breakeven Lyte.xlsx (22.2 KB, 7 views)
#2
07-28-2016, 09:31 AM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,068

I am not sure what you want to sum.

What your formula actually does is the following:
Sum from Sales!B19, go zero rows down and zero columns to the right and choose a height of 1+ F4, that is 3 and choose a width of 1, that is =SUM(Sales!B19:B21) = 39520.
#3
07-28-2016, 10:46 AM
 Erickd90 Windows 10 Office 2016 Novice Join Date: Jul 2016 Posts: 4

What I want to do is take B19 from the sales tab and multiply that by the number in F4 on my Breakeven tab.

If f4 on the Breakeven tab lists 0 or is empty, I need it to list the value of B19 on the sales tab.

Example...

Breakeven!F4 = 0

Sales!B19 = 1000

Breakeven!B19 = 1000

If Breakeven!F4 is changed to 1....

Breakeven!F4 = 1

Sales!B19 = 1000

Breakeven!B19 = 2000

If we change Breakeven!F4 to 5

Breakeven!F4 = 5

Sales!B19 = 1000

Breakeven!B19 = 5000
#4
07-28-2016, 11:14 AM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,068

Breakeven!F4 = 1

Sales!B19 = 1000

Breakeven!B19 = 2000

and
If we change Breakeven!F4 to 5

Breakeven!F4 = 5

Sales!B19 = 1000

Breakeven!B19 = 5000

1: 2000
2: 3000
3: 4000
4: 5000
5: 6000

or what is the pattern?

Perhaps like the attached.
Attached Files
 Breakeven Lyte_2.xlsx (22.2 KB, 2 views)
#5
07-28-2016, 11:27 AM
 Erickd90 Windows 10 Office 2016 Novice Join Date: Jul 2016 Posts: 4

XOR,

0: 1000
1: 2000
2: 3000
4: 5000
5: 6000

I have used an IF statement to complete everything except I am not sure how to avoid the multiplying by 0...
#6
07-28-2016, 11:30 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,452

As usual nobody's interested in my answer . ( not even acknowledged)

I will delete it then to make the thread easier to read.

Note to OP : don't bother to answer, adding you to my Ignore list
#7
07-28-2016, 12:09 PM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,068

So your wanted result in B19 (message at 7:48 PM) was incorrect as regards F4 =5.
Attached Files
 Breakeven Lyte_3.xlsx (22.3 KB, 1 views)
#8
07-28-2016, 12:20 PM
 Erickd90 Windows 10 Office 2016 Novice Join Date: Jul 2016 Posts: 4

My first impression is that this is exactly what I needed.

I am just getting started on some of this excel stuff and have been banging my head against a brick wall on this for a bit.

Thank You for the help!

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post nfsmith Excel 8 03-23-2016 11:28 PM Mango123 Excel 4 03-18-2014 02:52 PM dlowrey Excel 3 07-09-2013 09:15 AM Berk21 Excel 7 01-15-2012 11:41 AM tinfanide Excel Programming 0 12-09-2011 02:11 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 04:22 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top