indicate infinite range

AbelGoodwin1988

New Member
Joined
Jun 18, 2012
Messages
20
Hello!,
I have two questions.
Excel 2007
BCDEFGHI
1# of PurchasesFoodCostPrice/UnitUnitsCostPrice/UnitUnits
24/4/20124/16/2012
3Apples, Gala #2.550.785.560.78

<tbody>
</tbody>
Grocery Mastersheet
My wife and I are learning to use some excel and we decided a fun practice might be compiling data with grocery shopping.
One:
What i'm trying to do here is in the column # of purchases help me return the number of times that I have purchased a good.
For example if I made a purchase on 4/4/2012 but not on 4/16/2012 it would be 1. If it was purchased both dates then it would be two.
I'm assuming i'll use something along the lines of COUNTIF. I'm trying to do something like, COUNTIF(C3:AA3,?)/3.
So that question number one.
Two:
I want to, in the units column, have it divide the cost by price/unit to determine the # of units or lbs of something we bought. On some of the cost and price/unit sections though, there is nothing; which returns a div/0 error. I'm assuming i can use an IF formula somehow to say if in these two cells there is a number then divide, if not "".
So, any pointers would be much appreciated!
Man Excel is so fun!
Thank you, again!
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

AbelGoodwin1988

New Member
Joined
Jun 18, 2012
Messages
20
Well, I think i've solve the divide problem for myself, but I'm wondering if there is an alternate or better way to do it. I did:
=IF(E3>0,D3/E3,"")
 

Soggy

Board Regular
Joined
May 16, 2012
Messages
188
I'm not sure what you mean by question 1 but if you are countng the number of dates that appear in a column B then =counta(B:B) this counts the number of non blanks.

for question 2 you need to add an if statement so =IF(D2="","",D2/F2). The "" means the cell is blank.

Not sure if it is my computer but the formatting above looks odd so it's hard to see what is in what cell.
 

AbelGoodwin1988

New Member
Joined
Jun 18, 2012
Messages
20
I agree, the formatting looks a bit odd.. I wonder what my HTML converter did weird.
Thank you for the IF statement! that's a big help!
I'll try to restate my #1.
If there was a purchase on a particular date of any product then the 3 cells under that date in the row of the product will be filled. So I want to count all the cells in that row that are filled and then divide by 3.
 

AbelGoodwin1988

New Member
Joined
Jun 18, 2012
Messages
20

ADVERTISEMENT

doh! I also just remembered why I titled the thread as I did, sorry.
I'm wondering if it is possible to do a range as something like, D3:(Infinity)3
So that it just keeps going all the way to the right?
 

AbelGoodwin1988

New Member
Joined
Jun 18, 2012
Messages
20
Figured it out!
Just using Counta(range)/3
But is there a way to set the range to infinity for column, for row, or both?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,763
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

To find an unknown amount of data in columns, you would need to use VBA to return the last used column.
OR without following the rest of your posts you could use this to find the last numeric in row 1, for example
Code:
=INDEX(1:1,MATCH(9.99999999999999E+307,1:1))
 
Last edited:

AbelGoodwin1988

New Member
Joined
Jun 18, 2012
Messages
20
Thank you, AMAS.
Reading, right now!
I really appreciate all the help, everyone. I'm anxious to learn and maybe just don't have all the resources at this time.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,911
Messages
5,598,819
Members
414,260
Latest member
joishe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top