#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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. |
#5
|
|||
|
|||
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... |
#6
|
||||
|
||||
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
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
|||
|
|||
So your wanted result in B19 (message at 7:48 PM) was incorrect as regards F4 =5.
|
#8
|
|||
|
|||
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 | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
pull data using drop down menu | nfsmith | Excel | 8 | 03-23-2016 11:28 PM |
Data validation list filter with range defined by OFFSET | Mango123 | Excel | 4 | 03-18-2014 02:52 PM |
Syntax to use OFFSET() in a cell formula? | dlowrey | Excel | 3 | 07-09-2013 09:15 AM |
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 |