Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Solved! Excel: Formulas and increasing one column value
New Posts  All Forums:Forum Nav:

Solved! Excel: Formulas and increasing one column value

post #1 of 12
Thread Starter 
Solved!
New question posted two down.




So, I'm trying to create a spreadsheet to help keep track of how much money I'm bringing in from various positions in the restaurant I work in. I have the basic premise, I believe, for how they should look, but when I go to "drag" the formula down, the formula doesn't change with how I would expect it to. Can someone help me figure out why? Or am I going to have to retype the formula 120 times every time I alter it?



Example Formula:
Code:
=((SUM(B2:B2)*5+SUM(C2:C2))*0.86+SUM(D2:D2)-SUM(C2:C2))
I would like it to increase by 1 for each cell down, so the next would be:

Code:
=((SUM(B2:B3)*5+SUM(C2:C3))*0.86+SUM(D2:D3)-SUM(C2:C3))
With the last being:

Code:
=((SUM(B2:B32)*5+SUM(C2:C32))*0.86+SUM(D2:D32)-SUM(C2:C32))
This is my first time trying to create a real spreadsheet in Excel, any help is appreciated.
Edited by stu. - 10/22/11 at 9:45pm
 
F@H
(14 items)
 
 
CPUMotherboardGraphicsRAM
Core i7 920 @ 4.0ghz EVGA x58 132-BL-E758-A1 2x EVGA 460 1 GB G.SKILL 3 x 2 GB 
Hard DriveOSMonitorKeyboard
Mushkin 40 GB SSD / WD Black 1 TB Windows 7 Home Premium 64-bit DCLCD 20.1" Logitech G15 
PowerCaseMouse
Antec TP 750 Antec 900 Logitech G5 
CPUMotherboardRAMCooling
i7 2700k ASUS Maximus Gene-Z z68 G.Skill 2133mhz Noctua NH-D14 
OSPowerCase
Ubuntu 10.10 BFG 650 Silverstone TJ08-E 
  hide details  
Reply
 
F@H
(14 items)
 
 
CPUMotherboardGraphicsRAM
Core i7 920 @ 4.0ghz EVGA x58 132-BL-E758-A1 2x EVGA 460 1 GB G.SKILL 3 x 2 GB 
Hard DriveOSMonitorKeyboard
Mushkin 40 GB SSD / WD Black 1 TB Windows 7 Home Premium 64-bit DCLCD 20.1" Logitech G15 
PowerCaseMouse
Antec TP 750 Antec 900 Logitech G5 
CPUMotherboardRAMCooling
i7 2700k ASUS Maximus Gene-Z z68 G.Skill 2133mhz Noctua NH-D14 
OSPowerCase
Ubuntu 10.10 BFG 650 Silverstone TJ08-E 
  hide details  
Reply
post #2 of 12
if you want to lock a cell into an equation surround the letter portion of the cell with $

ex.

Code:
B3 = $A$1 / C1

when you drag it down the next will be

B4 = A1 / C2
B5 = A1 / C3
...
and so on

You might have to manually type your equation into cells P2 and P3...highlight both then drag all the way down...excel will follow the pattern.
Edited by surfbumb - 10/22/11 at 8:30pm
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
post #3 of 12
Thread Starter 
Incredible! That worked perfectly!

Now, building on that previous formula, is there a way that if a ROW is not filled out, that there is no number in the TOTAL (which is currently broken down into individual Serving/Takeaway/Hosting/Bussing) column? Here is a picture of what the sheet looks like now:



Such that, as I add a new day to the chart, the formula will respond accordingly... if that makes sense. But prior to the formula being necessary (the day has not happened yet...), the spots remain blank.


Edited by stu. - 10/22/11 at 8:43pm
 
F@H
(14 items)
 
 
CPUMotherboardGraphicsRAM
Core i7 920 @ 4.0ghz EVGA x58 132-BL-E758-A1 2x EVGA 460 1 GB G.SKILL 3 x 2 GB 
Hard DriveOSMonitorKeyboard
Mushkin 40 GB SSD / WD Black 1 TB Windows 7 Home Premium 64-bit DCLCD 20.1" Logitech G15 
PowerCaseMouse
Antec TP 750 Antec 900 Logitech G5 
CPUMotherboardRAMCooling
i7 2700k ASUS Maximus Gene-Z z68 G.Skill 2133mhz Noctua NH-D14 
OSPowerCase
Ubuntu 10.10 BFG 650 Silverstone TJ08-E 
  hide details  
Reply
 
F@H
(14 items)
 
 
CPUMotherboardGraphicsRAM
Core i7 920 @ 4.0ghz EVGA x58 132-BL-E758-A1 2x EVGA 460 1 GB G.SKILL 3 x 2 GB 
Hard DriveOSMonitorKeyboard
Mushkin 40 GB SSD / WD Black 1 TB Windows 7 Home Premium 64-bit DCLCD 20.1" Logitech G15 
PowerCaseMouse
Antec TP 750 Antec 900 Logitech G5 
CPUMotherboardRAMCooling
i7 2700k ASUS Maximus Gene-Z z68 G.Skill 2133mhz Noctua NH-D14 
OSPowerCase
Ubuntu 10.10 BFG 650 Silverstone TJ08-E 
  hide details  
Reply
post #4 of 12
Quote:
Originally Posted by stu. View Post
Incredible! That worked perfectly!

Now, building on that previous formula, is there a way that if that ROW is not filled out, that there is not number in the TOTAL (which is currently broken down into individual Serving/Takeaway/Hosting/Bussing) column? Here is a picture of what the sheet looks like now:

this gets a little harder... Put your 3 cells in the AND statement...put whatever formula you need after the AND statement...which occurs when all the conditions are greater than 0 or whatever condition you want....Empty is only displayed when a cell or cells is greater than 0.

ex.

Code:
C4 = IF(AND(Cell 1 > 0, Cell 2 > 0 , Cell 3 > 0 ), do formula here ,"Empty")
edit...just looked at your previous post...use cell 1 > 0...and put the false statement as 0...you can keep adding cells into the AND statement...i believe it holds up to 31 cells.

Code:
C4 = IF(AND(Cell 1 > 0, Cell 2 > 0 , Cell 3 > 0 ), put sum formula here , 0)

Edited by surfbumb - 10/22/11 at 9:05pm
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
post #5 of 12
Thread Starter 
Definitely getting closer to what I have in my head! Now, I'm not sure if this is a limitation due to Excel, or perhaps, I can further complicate things.

I am now using the following formula for my Serving money:

Code:
=IF(AND(B4 = 0, C4 = 0, D4 = 0 ),,((SUM($B$2:B4)*5+SUM($C$2:C4))*0.86+SUM($D$2:D4)-SUM($C$2:C4)))
So, the formula works: logic statement, value if false, value if true.

The logic statement works just fine, and the value if true works perfectly. I would like the value if false to return a null, preferably, blank, cell. It currently returns a 0. Is this possible to do?

edit: Thank you so much for your help and patience!
 
F@H
(14 items)
 
 
CPUMotherboardGraphicsRAM
Core i7 920 @ 4.0ghz EVGA x58 132-BL-E758-A1 2x EVGA 460 1 GB G.SKILL 3 x 2 GB 
Hard DriveOSMonitorKeyboard
Mushkin 40 GB SSD / WD Black 1 TB Windows 7 Home Premium 64-bit DCLCD 20.1" Logitech G15 
PowerCaseMouse
Antec TP 750 Antec 900 Logitech G5 
CPUMotherboardRAMCooling
i7 2700k ASUS Maximus Gene-Z z68 G.Skill 2133mhz Noctua NH-D14 
OSPowerCase
Ubuntu 10.10 BFG 650 Silverstone TJ08-E 
  hide details  
Reply
 
F@H
(14 items)
 
 
CPUMotherboardGraphicsRAM
Core i7 920 @ 4.0ghz EVGA x58 132-BL-E758-A1 2x EVGA 460 1 GB G.SKILL 3 x 2 GB 
Hard DriveOSMonitorKeyboard
Mushkin 40 GB SSD / WD Black 1 TB Windows 7 Home Premium 64-bit DCLCD 20.1" Logitech G15 
PowerCaseMouse
Antec TP 750 Antec 900 Logitech G5 
CPUMotherboardRAMCooling
i7 2700k ASUS Maximus Gene-Z z68 G.Skill 2133mhz Noctua NH-D14 
OSPowerCase
Ubuntu 10.10 BFG 650 Silverstone TJ08-E 
  hide details  
Reply
post #6 of 12
Quote:
Originally Posted by stu. View Post
Definitely getting closer to what I have in my head! Now, I'm not sure if this is a limitation due to Excel, or perhaps, I can further complicate things.

I am now using the following formula for my Serving money:

Code:
=IF(AND(B4 = 0, C4 = 0, D4 = 0 ),,((SUM($B$2:B4)*5+SUM($C$2:C4))*0.86+SUM($D$2:D4)-SUM($C$2:C4)))
So, the formula works: logic statement, value if false, value if true.

The logic statement works just fine, and the value if true works perfectly. I would like the value if false to return a null, preferably, blank, cell. It currently returns a 0. Is this possible to do?

edit: Thank you so much for your help and patience!
it seems you should use
Code:
= IF(AND(B4 > 0, C4 > 0, D4 > 0), true statement here, false statement here))
your true statement being the sum formula...and false statement being blank or 0...i think for blank...it would be kind of difficult and pointless if this is something just for your benefit..
Edited by surfbumb - 10/22/11 at 9:27pm
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
post #7 of 12
Thread Starter 
Er, you are correct. In my head however, I am reading things differently. Yes, the order is logic/true/false, but due to the nature of the logic statement (when the values = 0), the summation will happen under the false condition.

Yes, this is my own little project, but I am also enjoying messing around with Excel. Thank you for everything you've been able to offer! I am slightly OCD, which is why I would love to eliminate the 0s. During a full month of work, I feel like the daily totals may just seem slightly bloated, and difficult to quickly read with all of the 0s.

EDIT: SOLVED! I used what you had earlier, and replaced "Empty" with " ", which was enough for Excel apparently. This has therefore created the impression of a "blank" cell.
Edited by stu. - 10/22/11 at 9:28pm
 
F@H
(14 items)
 
 
CPUMotherboardGraphicsRAM
Core i7 920 @ 4.0ghz EVGA x58 132-BL-E758-A1 2x EVGA 460 1 GB G.SKILL 3 x 2 GB 
Hard DriveOSMonitorKeyboard
Mushkin 40 GB SSD / WD Black 1 TB Windows 7 Home Premium 64-bit DCLCD 20.1" Logitech G15 
PowerCaseMouse
Antec TP 750 Antec 900 Logitech G5 
CPUMotherboardRAMCooling
i7 2700k ASUS Maximus Gene-Z z68 G.Skill 2133mhz Noctua NH-D14 
OSPowerCase
Ubuntu 10.10 BFG 650 Silverstone TJ08-E 
  hide details  
Reply
 
F@H
(14 items)
 
 
CPUMotherboardGraphicsRAM
Core i7 920 @ 4.0ghz EVGA x58 132-BL-E758-A1 2x EVGA 460 1 GB G.SKILL 3 x 2 GB 
Hard DriveOSMonitorKeyboard
Mushkin 40 GB SSD / WD Black 1 TB Windows 7 Home Premium 64-bit DCLCD 20.1" Logitech G15 
PowerCaseMouse
Antec TP 750 Antec 900 Logitech G5 
CPUMotherboardRAMCooling
i7 2700k ASUS Maximus Gene-Z z68 G.Skill 2133mhz Noctua NH-D14 
OSPowerCase
Ubuntu 10.10 BFG 650 Silverstone TJ08-E 
  hide details  
Reply
post #8 of 12
Quote:
Originally Posted by stu. View Post
Er, you are correct. In my head however, I am reading things differently. Yes, the order is logic/true/false, but due to the nature of the logic statement (when the values = 0), the summation will happen under the false condition.

Yes, this is my own little project, but I am also enjoying messing around with Excel. Thank you for everything you've been able to offer! I am slightly OCD, which is why I would love to eliminate the 0s. During a full month of work, I feel like the daily totals may just seem slightly bloated, and difficult to quickly read with all of the 0s.
you can try using quotes with a blank space between them like this:

Code:
= IF(AND(B4 > 0, C4 > 0, D4 > 0), true statement here, "      "))
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
post #9 of 12
Thread Starter 
Quote:
Originally Posted by surfbumb View Post
you can try using quotes with a blank space between them like this:

Code:
= IF(AND(B4 > 0, C4 > 0, D4 > 0), true statement here, "      "))
Haha, we posted/editted around the same exact time. Thank you so much for your patience with this matter! I'm very happy with how my project turned out tonight! Much appreciated.
 
F@H
(14 items)
 
 
CPUMotherboardGraphicsRAM
Core i7 920 @ 4.0ghz EVGA x58 132-BL-E758-A1 2x EVGA 460 1 GB G.SKILL 3 x 2 GB 
Hard DriveOSMonitorKeyboard
Mushkin 40 GB SSD / WD Black 1 TB Windows 7 Home Premium 64-bit DCLCD 20.1" Logitech G15 
PowerCaseMouse
Antec TP 750 Antec 900 Logitech G5 
CPUMotherboardRAMCooling
i7 2700k ASUS Maximus Gene-Z z68 G.Skill 2133mhz Noctua NH-D14 
OSPowerCase
Ubuntu 10.10 BFG 650 Silverstone TJ08-E 
  hide details  
Reply
 
F@H
(14 items)
 
 
CPUMotherboardGraphicsRAM
Core i7 920 @ 4.0ghz EVGA x58 132-BL-E758-A1 2x EVGA 460 1 GB G.SKILL 3 x 2 GB 
Hard DriveOSMonitorKeyboard
Mushkin 40 GB SSD / WD Black 1 TB Windows 7 Home Premium 64-bit DCLCD 20.1" Logitech G15 
PowerCaseMouse
Antec TP 750 Antec 900 Logitech G5 
CPUMotherboardRAMCooling
i7 2700k ASUS Maximus Gene-Z z68 G.Skill 2133mhz Noctua NH-D14 
OSPowerCase
Ubuntu 10.10 BFG 650 Silverstone TJ08-E 
  hide details  
Reply
post #10 of 12
Quote:
Originally Posted by stu. View Post
Haha, we posted/editted around the same exact time. Thank you so much for your patience with this matter! I'm very happy with how my project turned out tonight! Much appreciated.
haha nice...I'm glad to help another OCN member...just as I have been helped...enjoy your project!
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Solved! Excel: Formulas and increasing one column value