Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
sum using or (if Blank cell in Column)
My Basic problem seems to be that i cannot run a variable against a blank cell.
My set up as below: Item 50cl 70cl 1.5cl per 25ml (< answer i'm looking for) Rum £17.50 Cheese £80.35 Milk £10.50 Now individually i can work out these items. Rum would be 0.62 in the 25ml column... but I cannot find how to create each sum in the total box, so when i leave two of them blank it understand to only work out the one answer. It must look something like this? Right? Item 50cl 70cl 1.5cl per 25ml Rum £17.50 =sum(b2/20) (C2/28) (D2/60) Cheese £80.35 =sum(b3/20) (C3/28) (D3/60) Milk £10.50 =sum(b4/20) (C4/28) (D4/60) What am i missing in between each set of brackets to make the thing work though? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
sum using or (if Blank cell in Column)
The alignment is a bit out in your posting. Assume headings are in row
1, with Items in column A, 50 in cell B1, 70 in C1 and 1.5 in D1. In E2 type in this array formula*: =SUM(IF(B2:D2<0,B2:D2/B$1:D$1*2.5,0)) *As this is an array formula, when you have entered it (or subsequently edit it) you must use CTRL-SHIFT-ENTER instead of ENTER. If you do this correctly, then Excel will wrap curly braces { } around the formula - you must not type these yourself. Copy the formula down column E for as many rows as you have items. Hope this helps. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to index the first blank cell in a column of data | Excel Discussion (Misc queries) | |||
run a macro in a locked cell | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
How do I reference every "n" cell in a column in Excel? | Excel Worksheet Functions |