Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-28-2016, 08:01 AM
Erickd90 Erickd90 is offline I cant get my Sum(Offset formula to pull appropriate data. Windows 10 I cant get my Sum(Offset formula to pull appropriate data. Office 2016
Novice
I cant get my Sum(Offset formula to pull appropriate data.
 
Join Date: Jul 2016
Posts: 4
Erickd90 is on a distinguished road
Default 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
File Type: xlsx Breakeven Lyte.xlsx (22.2 KB, 7 views)
Reply With Quote
  #2  
Old 07-28-2016, 09:31 AM
xor xor is offline I cant get my Sum(Offset formula to pull appropriate data. Windows 10 I cant get my Sum(Offset formula to pull appropriate data. Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,068
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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.
Reply With Quote
  #3  
Old 07-28-2016, 10:46 AM
Erickd90 Erickd90 is offline I cant get my Sum(Offset formula to pull appropriate data. Windows 10 I cant get my Sum(Offset formula to pull appropriate data. Office 2016
Novice
I cant get my Sum(Offset formula to pull appropriate data.
 
Join Date: Jul 2016
Posts: 4
Erickd90 is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 07-28-2016, 11:14 AM
xor xor is offline I cant get my Sum(Offset formula to pull appropriate data. Windows 10 I cant get my Sum(Offset formula to pull appropriate data. Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,068
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

I don't understand your logic.

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
File Type: xlsx Breakeven Lyte_2.xlsx (22.2 KB, 2 views)
Reply With Quote
  #5  
Old 07-28-2016, 11:27 AM
Erickd90 Erickd90 is offline I cant get my Sum(Offset formula to pull appropriate data. Windows 10 I cant get my Sum(Offset formula to pull appropriate data. Office 2016
Novice
I cant get my Sum(Offset formula to pull appropriate data.
 
Join Date: Jul 2016
Posts: 4
Erickd90 is on a distinguished road
Default

XOR,

The pattern would follow this...

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...
Reply With Quote
  #6  
Old 07-28-2016, 11:30 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline I cant get my Sum(Offset formula to pull appropriate data. Windows 7 64bit I cant get my Sum(Offset formula to pull appropriate data. Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,452
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

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
Reply With Quote
  #7  
Old 07-28-2016, 12:09 PM
xor xor is offline I cant get my Sum(Offset formula to pull appropriate data. Windows 10 I cant get my Sum(Offset formula to pull appropriate data. Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,068
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

So your wanted result in B19 (message at 7:48 PM) was incorrect as regards F4 =5.
Attached Files
File Type: xlsx Breakeven Lyte_3.xlsx (22.3 KB, 1 views)
Reply With Quote
  #8  
Old 07-28-2016, 12:20 PM
Erickd90 Erickd90 is offline I cant get my Sum(Offset formula to pull appropriate data. Windows 10 I cant get my Sum(Offset formula to pull appropriate data. Office 2016
Novice
I cant get my Sum(Offset formula to pull appropriate data.
 
Join Date: Jul 2016
Posts: 4
Erickd90 is on a distinguished road
Default

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!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
I cant get my Sum(Offset formula to pull appropriate data. pull data using drop down menu nfsmith Excel 8 03-23-2016 11:28 PM
I cant get my Sum(Offset formula to pull appropriate data. Data validation list filter with range defined by OFFSET Mango123 Excel 4 03-18-2014 02:52 PM
I cant get my Sum(Offset formula to pull appropriate data. Syntax to use OFFSET() in a cell formula? dlowrey Excel 3 07-09-2013 09:15 AM
I cant get my Sum(Offset formula to pull appropriate data. sumproduct formula to pull info from multiple sheets Berk21 Excel 7 01-15-2012 11:41 AM
Excel VBA: Pull data from web 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.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft