Forum Discussion
Creating a formula when the relevent 'cell' displays differently than its data
In 'column A' I have a formula for example "=sum(A365,1)".
The 'category' of the cell is number:custom:DDD giving a visable result of, for example, "Mon".
In 'column D' I wish to place a (Logical?) formula which reads the cells in 'column A' and gives a particular result when true, for example when 'A366' = "Mon" give a result of "10", when 'A366' = "Tue" give a result of "38.45", etc.
I suspect I would need to use an IFS formula, but the problem is, the formula doesn't read the cell as "Mon", it reads it as "=sum(A365,1)",giving it an always false result.
I thought perhaps that i could do something like if 'A366'=divisable by 7... or if 'A366'-1=divisable by 7 etc... but I dont really know how i would achieve that idea
Does anyone have a recommendation of how i can resolve this problem?
Thankyou for your help
3 Replies
Ah-ha, thankyou SergeiBaklan ,
I found that =WEEKDAY(A366+1) was necessary, =WEEKDAY(A366,1) gave the same day as A366 and =WEEKDAY(A366,2) gave a result of day earlier, =WEEKDAY(A366,3) gave a result of 2 days earlier! No idea why.
Also making this adjustment in Column A had no effect on getting a favourable result for Column D.
Do you have any recommendations for the Column D formula?
Many thanks
Could you please submit small sample file with just couple of records, with A365 and A366. It's not clear is that numbers or dates, how they are formatted.
