I have a large spreadsheet that contains revenue information tied to sales deals.
Column A contains the expected deal closing date and column D the deal amount. I'm trying to classify the amounts into Quarterly Revenue columns (R,S,T,U). To do this I generated the following formula:
As I copy the formula from column R (Q1) to column S (Q2), the formula returns a true value and copies the amount on the next quarter column, as well. My assumption is that the way Excel is processing the formula is that it processes these conditions (greater than or equal AND less than or equal) and treats them as OR, thus if the A column value meets either, it considers them as TRUE.
I hope this explanation is clear and I'd truly appreciate some help on it.
Hi, thanks for your prompt replay. I finally solved the problem, although I had to solve it in stages using the formula assistant from Excel. First I made sure the AND function was correct and then I added in the IF function.
As it turned out, the problem was not with the formula but with the Date format. What I found out is that if the date formats you're trying to compare are different (in this case one format was in Spanish and the other in US English) then you will either get a VALUE error or a wrong comparison. A 04/01/14 date, for example would read Jan 1 in Spanish and April 1 in US English.